Geek Answers Handbook

Check multiple lines for value, just return a line with MAX / MIN

I am trying to write a query that will compare the value N of the number of rows and return only the row with the maximum value. For example, if I only wanted to return a table with no duplicate rows, but only a row with the most recent date -

key  | name  | value |   date
 1   | frank |  100  | 1/1/2013
 2   | peter |  200  | 2/1/2013
 3   | jonny |  300  | 3/1/2013
 4   | jonny |  300  | 4/1/2013

And request:

SELECT key, name, value, MAX(date)
FROM myTable
WHERE key IN (1,2,3,4)

I expect this to return

key  | name  | value |   date
 1   | frank |  100  | 1/1/2013
 2   | peter |  200  | 2/1/2013
 4   | jonny |  300  | 4/1/2013

I'm not sure how to use GROUP BY, I think I missed something fundamental with my attempts.

+4
source share
2 answers

Well, if you need only the newest line, you can use the following:

SELECT TOP 1 key, name, value, date
FROM myTable
ORDER BY date desc

This should return a single row with the newest date in this table.

, :

SELECT name, max(date)
FROM myTable
WHERE key in(1,2,3,4)
GROUP BY name

- . , , , , group by.

, , , , :

;with namesWithMaxDate as(
 select
   name
  ,max(date) as date
 from
   myTable 
 group by
   name 
 )
 select 
   myTable.[key]
  ,myTable.name
  ,myTable.value
  ,myTable.date
 from myTable
 inner join
 namesWithMaxDate 
 on 
   myTable.name = namesWithMaxDate.name and 
   myTable.date = namesWithMaxDate.date

, , , . , .

: .

 select 
   myTable.[key]
  ,myTable.name
  ,myTable.value
  ,myTable.date
 from myTable
  inner join
 (   select
       name
      ,max(date) as date
     from
       myTable 
     group by
       name ) as namesWithMaxDate
 on 
   myTable.name = namesWithMaxDate.name and 
   myTable.date = namesWithMaxDate.date

. .

+6

:

SELECT a.key, a.name, a.value, a.date
FROM myTable a 
WHERE a.key IN (1,2,3,4)
and 
a.DATE = (select MAX(date) from myTable b where a.key = b.key)
+2

More articles:


All Articles