t-sql select help

Question

Post reply

t-sql select help

rpatil22

Ten Centuries

Points: 1371
More actions
April 21, 2009 at 11:33 am

#130204

I have a table with data. Daily new rows are added.
How to select first 2 rows having max date?
Date Id Name
2009-04-21 2 East
2009-04-10 2 East
2009-04-19 2 East
2009-04-18 2 East
2009-04-13 2 East
I want to select the 2 rows having max date. Tomorrow there may be another row.
2009-04-21 2 East
2009-04-19 2 East

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

select the 2 rows having max date: TSQL pretty much writees the same way the question was outlined:

if there are two records with the same max date:

SELECT TOP 2 *

FROM YOURTABLE

WHERE YourDateField IN(SELECT MAX(YourDateField ) AS YourDateField FROM YOURTABLE)

or if you want just the two highest max dates, even if they are different:

SELECT TOP 2 *

FROM YOURTABLE

ORDER BY YourDateField DESC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rpatil22 Ten Centuries Points: 1371 More actions · Answer 2

I want first max date rows of different names?

Date Id Name

2009-04-21 2 East

2009-04-10 2 East

2009-04-19 2 East

2009-04-18 2 East

2009-04-13 2 East

2009-04-20 3 West

2009-04-10 3 West

2009-04-17 3 West

2009-04-16 3 West

2009-04-13 3 West

Output needed

2009-04-21 2 East

2009-04-19 2 East

2009-04-20 3 West

2009-04-17 3 West

I want to select the 2 rows having max date. Tomorrow there may be another row.

2009-04-21 2 East

2009-04-19 2 East

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi

You can use a ROW_NUMBER and build partitions:

DECLARE @t TABLE (Date DATETIME, Id INT, Name VARCHAR(10))

INSERT INTO @t

SELECT '2009-04-21', '2', 'East'

UNION ALL SELECT '2009-04-10', '2', 'East'

UNION ALL SELECT '2009-04-19', '2', 'East'

UNION ALL SELECT '2009-04-18', '2', 'East'

UNION ALL SELECT '2009-04-13', '2', 'East'

UNION ALL SELECT '2009-04-20', '3', 'West'

UNION ALL SELECT '2009-04-10', '3', 'West'

UNION ALL SELECT '2009-04-17', '3', 'West'

UNION ALL SELECT '2009-04-16', '3', 'West'

UNION ALL SELECT '2009-04-13', '3', 'West'

; WITH

cte (Date, Id, Name, RowId) AS

(

SELECT

*,

ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC) Position

FROM @t

)

SELECT *

FROM cte

WHERE RowId <= 2

Greets

Flo