Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Server Select MAX

Posted on 2012-08-22
Medium Priority
Last Modified: 2012-08-22
I have a table containing Serial Numbers many of which have multiple Ids:

Id   SerialNumbers
1     002561
2     002561
3     002569
4     002569
5     002569
6     005698
7     007120
8     007120

I need to write a SQL Server query to produce a new table containing just the serial number entries with the highest id's so that, in the above list, only rows with these Id's would be selected:   2, 5, 6 & 8.

I've tried various queries using MAX, GROUP BY & HAVING but I haven't come up with a solution.  What's the best way of doing this?
Question by:ccravenbartle
LVL 25

Assisted Solution

lwadwell earned 1000 total points
ID: 38319385
Did you try
SELECT max(Id) as Id, SerialNumbers
FROM your_table
GROUP BY SerialNumbers
LVL 12

Accepted Solution

Habib Pourfard earned 1000 total points
ID: 38319396
SELECT id , SerialNumbers
FROM (SELECT id , SerialNumbers , row_number() OVER (PARTITION BY SerialNumbers ORDER BY id DESC) RowNo FROM YourTable) T
WHERE T.RowNo = 1

Open in new window

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38319442
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.


Expert Comment

ID: 38319981
SELECT MAX(id) FROM #tbl GROUP BY SerialNumbers

Open in new window


Expert Comment

by:Anurag Agarwal
ID: 38320241
SELECT MAX(id) id  from tbl
group by serialnumbers

Expert Comment

by:Vijaya Reddy Pinnapa Reddy
ID: 38320987
Select max(id) as ID from tablename group by SerialNumbers

Author Closing Comment

ID: 38321032
Thank you all for your responses.   Much appreciated.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question