CODEWITHSUNDEEP
sql
Nil Pun
Nil Pun

Reputation: 17373

SQL Get Last Record By Date

I have a table like this:

InstallID   |InstallationDate
1           |01-01-2014
1           |01-02-2014
1           |01-03-2014
1           |01-04-2014
2           |01-01-2014
2           |01-02-2014
3           |01-01-2014
3           |01-02-2014
3           |01-03-2014
4           |01-04-2014
4           |01-05-2014

where I need to get the latest installationDate for each Installation ID

e.g.

InstallID   |InstallationDate
1           |01-04-2014
2           |01-02-2014
3           |01-03-2014
4           |01-05-2014

Could someone help me how above can be achieve using SQL?

Upvotes: 0

Views: 176

Answers (7)

Angelo
Angelo

Reputation: 335

i think s simpliest way is 

SELECT t1.InstallID, 
   MAX(t1.InstallationDate)
FROM yourTable t1
Group by t1.InstallID

Upvotes: 0

Justin
Justin

Reputation: 9724

Query:

SQLFiddleExample

SELECT t1.InstallID, 
       t1.InstallationDate
FROM yourTable t1
 LEFT JOIN yourTable t2
  ON t1.InstallID = t2.InstallID
  AND t1.InstallationDate < t2.InstallationDate
WHERE t2.InstallID is null

Result:

| INSTALLID |               INSTALLATIONDATE |
|-----------|--------------------------------|
|         1 | January, 04 2014 00:00:00+0000 |
|         2 | January, 02 2014 00:00:00+0000 |
|         3 | January, 03 2014 00:00:00+0000 |
|         4 | January, 05 2014 00:00:00+0000 |

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

By Group By

SELECT InstallID,MAX(InstallationDate)
FROM TABLE1
GROUP BY InstallID

(or)

By Row_Number

SELECT InstallID,InstallationDate FROM
(
SELECT InstallID,InstallationDate,
       ROW_NUMBER() OVER (PARTITION BY InstallID,InstallationDate ORDER BY InstallID) AS RN,
       COUNT(*) over (PARTITION BY InstallID,InstallationDate) as CN
FROM Table1 
) AS T WHERE RN = CN

Upvotes: 0

Purushotham
Purushotham

Reputation: 3820

Try this

SELECT InstallID, MAX(InstallationDate)
FROM <TableName>
GROUP BY InstallID

Upvotes: 0

Dba
Dba

Reputation: 6639

Try this,

SELECT installid,
       MAX(installationdate)
FROM <table_name>
GROUP BY installid

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Select InstallID   , MAX(InstallationDate)
From yourTable
Group  By InstallID

Upvotes: 0

TechDo
TechDo

Reputation: 18639

Please try:

SELECT
    InstallID,
    MAX(InstallationDate)
FROM
    YourTable
GROUP BY InstallID

Upvotes: 0

Related Questions