CODEWITHSUNDEEP
sql-server
Hikaru Shindo
Hikaru Shindo

Reputation: 2913

SQL Server Group By Based on one column

Before asking a question, I'd like to inform everyone that I've been trying to find the solutions on Google and here. Unfortunately, I'm still seriously stuck with this. I am a newbie of SQL Server (Normally use MySQL & MongoDB). I have to migrate DB from MySQL to SQL Server.

I want to use a GROUP BY item_id. I can do well in MySQL but it is not working on SQL Server. Here is my example data,

╔════╦═══════╦════════════╦════════════╦═══════════════════════╗
║ id ║item_id║    lat     ║     lng    ║       timestamp       ║
╠════╬═══════╬════════════╬════════════╬═══════════════════════╣
║  1 ║ a1    ║51.42743450 ║-0.72776696 ║2017-07-05 09:54:49.000║
║  2 ║ a1    ║51.59665507 ║-0.72777098 ║2017-07-05 11:54:49.000║
║  3 ║ b2    ║51.59664690 ║-0.67272032 ║2016-08-10 10:11:49.000║
║  4 ║ b2    ║51.59664874 ║-0.67270288 ║2016-08-10 11:05:49.000║
║  5 ║ b2    ║51.59665167 ║-0.67271587 ║2016-08-10 10:08:49.000║
╚════╩═══════╩════════════╩════════════╩═══════════════════════╝

Here is my MySQL code, 

SELECT item_id, lat, lng, MAX(timestamp) AS latest FROM record GROUP BY item_id;

It will correctly give me,

╔═══════╦════════════╦════════════╦═══════════════════════╗
║item_id║    lat     ║     lng    ║       latest          ║
╠═══════╬════════════╬════════════╬═══════════════════════╣
║ a1    ║51.59665507 ║-0.72777098 ║2017-07-05 11:54:49.000║
║ b2    ║51.59664874 ║-0.67270288 ║2016-08-10 11:05:49.000║
╚═══════╩════════════╩════════════╩═══════════════════════╝

But When I tried in SQL Server it is not working. It gave me every row in the table. He is the SQL Server code,

SELECT asset_id, lat, lng, MAX(timestamp) AS latest FROM 
mydb.record 
GROUP BY asset_id, lat, lng

I have also tried this that I've found on Stackoverflow (Which I am really confused),

SELECT a.asset_id, a.lat, a.lng FROM mydb.record a
INNER JOIN (
    SELECT asset_id, MAX(timestamp) latest FROM mydb.record
    GROUP BY asset_id
) b
ON a.asset_id = b.asset_id

It's still not working. I'm really stuck with SQL Server world. Please someone helps me. Thanks.

Upvotes: 1

Views: 44

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use row_number as below:

Select top (1) with ties item_id, lat, lng, latest
    from yourtable
Order by Row_Number() over(partition by item_id order by latest desc)

Other way

Select item_id, lat, lng, latest from (
   Select *, RowN = Row_Number() over(partition by item_id order by latest desc) from mydb.record --yourtable
 ) a where a.RowN = 1

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

In sql server, mydb.record will be interpreted as

schema Name : Mydb

Table Name : record

If am not wrong Mydb is database name so it should Mydb.schemaname.record

SELECT a.asset_id, a.lat, a.lng FROM mydb.record a
INNER JOIN (
    SELECT asset_id, MAX(timestamp) latest FROM Mydb.dbo.record --here
    GROUP BY asset_id
) b
ON a.asset_id = b.asset_id

By default sql server uses dbo schema, If your table is present in any other schema please use it instead of dbo

I would write this using Row_Number

select * from 
(
SELECT item_id, lat, lng, timestamp,
       Row_Number()Over(item_id order by timestamp desc) as Rn
FROM record
) a
Where Rn = 1

Upvotes: 0

Related Questions