Selecting specific data based on multiple conditions

Question

I need some help constructing a SQL command for a database query. The database has 5 columns:

Date(string) 
Name(string) 
number(int)

There can be multiple entries for each date, name, and number.

I want to SELECT only one row for each date and name combination. The problem is there are multiple instances of these. For each date and name combination I want to select the one with the highest number. I would like it ordered by date. For example:

date   |  name  |  number
1/1/1     henry      500
1/1/1     henry      2000 
1/1/1     jacob      5
1/1/1     jacob      8
1/2/1     henry      6

The command would return:

1/1/1     henry     2000
1/1/1     jacob     8 
1/2/1     henry     6

I have been messing around with some commands but I am a pretty lost. Is this even possible?

What SQL flavor (MySql, SQL Server, Oracle, etc.) are you using? What have you already tried? Do you know how to use `GROUP BY` clause? — PM 77-1, Mar 18 '13 at 22:05
SQL Server 2012. Yes I do. I think I see where you're going with that. I will give it a shot. — S1r-Lanzelot, Mar 18 '13 at 22:07
I posted what I had in mind. If you had other columns that you needed to see then the solution would not be that simple as mine. — PM 77-1, Mar 18 '13 at 22:12

Tim Schmelter · Accepted Answer · 2013-03-18T22:22:48.813

2

You can use ROW_NUMBER:

WITH cte 
     AS (SELECT date, 
                name, 
                number, 
                rn = Row_number () 
                       OVER( 
                         partition BY date, name 
                         ORDER BY number DESC) 
         FROM   dbo.tablename) 
SELECT date, 
       name, 
       number 
FROM CTE
WHERE  rn = 1 
ORDER  BY date ASC

DEMO

ROW_NUMBER will always select one record per group. If you want to get all rows with the highest number for a given name(if there are more than one) use DENSE_RANK instead.

edited Mar 18 '13 at 22:22

answered Mar 18 '13 at 22:05

Tim Schmelter

450,073
74
686
939

Thank you. However I am not sure I understand the WITH cte... can this be used in SQL Server? – S1r-Lanzelot Mar 18 '13 at 22:13
@40Alpha: Yes, that's TSQL which works in MS SQL-Server. It uses a [`Common Table Expression`](http://msdn.microsoft.com/en-us/library/ms175972.aspx) + [`ROW_NUMBER`](http://msdn.microsoft.com/en-us/library/ms186734.aspx) (which also exists in other rdbms like Oracle). I've used the CTE for simplification since they increase readability. – Tim Schmelter Mar 18 '13 at 22:16
Makes sense now haha! Thank you. I will let you know if it works out. – S1r-Lanzelot Mar 18 '13 at 22:26

susie derkins · Answer 2 · 2013-03-18T22:16:51.627

0

Try grouping by date and name and then selecting the maximum number. Like so (exact syntax may vary depending on your version of sql):

select
   date,
   name,
   max(number)
from
   yourtable
group by
   date,
   name
order by
   date asc

edited Mar 18 '13 at 22:16

answered Mar 18 '13 at 22:09

susie derkins

512
2
6
17

score 0 · Answer 3 · answered Mar 18 '13 at 22:10

0

SELECT date, name, MAX(number)
FROM Table1
GROUP BY date, name
ORDER date, name

answered Mar 18 '13 at 22:10

PM 77-1

12,933
21
68
111

Selecting specific data based on multiple conditions

3 Answers3