CODEWITHSUNDEEP
sqlsql-server
user2882307
user2882307

Reputation:

SQL advanced compare of strings

I am working on getting some very specific information out of a MSSQL database. All the information I need is available via this SQL command:

SELECT 
receivedate, 
swp.firmwarelevel AS 'Firmwarelevel',
pa.description AS 'Description' 
FROM tbl_swapgroup_parts swp, tbl_parts pa, tbl_part_type pt, tbl_swapgroups sw, tbl_workorders wo 
WHERE custom7 = 'somemodel' 
AND swp.swapgroup_id IN (18,25) 
AND sw.id = swp.swapgroup_id 
AND pa.id = swp.part_id 
AND pt.id = pa.parttype_id 
AND wo.part_id = pa.id'

Now this returns a list which looks something like this, trimmed to just 4 entries for sanity...

+---------------------+---------------+--------------------------------------------------+
|     receivedate     | Firmwarelevel |                   Description                    |
+---------------------+---------------+--------------------------------------------------+
| 2013-08-29 12:10:28 |      YN07     |         Description 1...                         |
| 2013-08-29 12:10:28 |      YN07     |         Description 2...                         |
| 2014-01-13 13:12:55 |      YN07     |         Description 1...                         |
| 2014-01-13 13:12:55 |      YN07     |         Description 2...                         |
+---------------------+---------------+--------------------------------------------------+

So what I want is really only 1 entry for each Unique description string, and the unique entry has to be the one with the newest recievedate.

Is such an 'advanced' thing possible only in SQL or do I have to post-process with some other language like python?

If at all possible I`d like to do it in pure SQL...

Upvotes: 0

Views: 87

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103467

Yes, you can do this in SQL. For example, with row_number():

SELECT * FROM (
  SELECT 
     receivedate, 
     swp.firmwarelevel AS 'Firmwarelevel',
     pa.description AS 'Description',
     row_number() over (partition by Description order by receivedate desc) rn
 FROM tbl_swapgroup_parts swp, 
      tbl_parts pa, 
      tbl_part_type pt, 
      tbl_swapgroups sw, 
      tbl_workorders wo 
 WHERE custom7 = 'somemodel' 
     AND swp.swapgroup_id IN (18,25) 
     AND sw.id = swp.swapgroup_id 
     AND pa.id = swp.part_id 
     AND pt.id = pa.parttype_id 
     AND wo.part_id = pa.id
) x
where x.rn=1

Also, ANSI JOIN syntax came out in 1992 - please use it :)

Upvotes: 1

Related Questions