Obtain the highest value name of a Sql Server query

Navigation
3

Good morning. I have a table in Sql Server that contains Id , Nombre , Numero . The names can be repeated but each will have a unique id , I need to get the names that have the number ( Phone ) greater.

I attach images to make it clearer.

this is what I need

What I need to be greater is the column Phone and that the three columns are shown.

This is how it works for me, but I do not like the idea of comparing by name. 

SELECT C.ShipperId, C.Shippername, C.Phone
FROM (
  SELECT MAX(A.Phone) AS Phone, A.shippername
  FROM table A
  GROUP BY A.shippername) B
JOIN table C
ON B.phone = C.phone
and B.shippername = C.shippername
    
asked by Victor 19.10.2016 в 23:35
source

3 answers

3

The "window function" row_number() is precisely designed for this type of situation: 

select t.ShipperID, t.ShipperName, t.Phone
  from (select t.*,
               row_number() over (partition by t.ShipperName order by t.Phone desc) as rn
          from tbl t) t
 where t.rn = 1
    
answered by 20.10.2016 / 00:04
source
0

group by ShipperName, getting the max ShipperID for each name, like this: 

select ShipperName, max(ShipperID)
  from MiTabla
 group by ShipperName
    
answered by 19.10.2016 в 23:38
0

You can get the highest Ids with the following query: 

SELECT TOP 2 * FROM Tabla
ORDER BY ShipperId DESC

With this, the result is sorted from highest to lowest and you select the two records (or the number of records wanted) higher.

Update

To take the Phone field as the maximum value, the following query can be helpful: 

SELECT * FROM Tabla
WHERE Phone = (SELECT MAX(Phone) FROM Tabla)
ORDER BY ShipperId
    
answered by 19.10.2016 в 23:40
Difference between RealmList and RealmResults in Realm.io of Android Pandas Load bad dates in Python 3