How Do I sort data From Table to get unique values

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

, +

I have table that has rows as below

SQL

DECLARE @Table TABLE (minv_code INT, alert_msg varchar(10), alert_time Datetime)    
INSERT INTO @Table VALUES
(873939, 'Reverse', '7/24/2015 3:31:18'), 
(873939, 'Tamper',  '7/24/2015 3:30:00'), 
(873939, 'Meter',   '7/24/2015 3:31:22'), 
(873940, 'Reverse', '7/24/2015 3:30:00'),
(873940, 'Tamper',  '7/24/2015 3:31:22')

I want to sort and select the data in such a way as to get one value per minv_code. The output should look like:

first row:       873939 'Meter'    '7/24/2015 3:31:22'
second row:      873940 'Tamper'   '7/24/2015 3:31:22'

then the rest of the columns
can somebody help me in this

Posted 3-Aug-15 8:20am

Member 11829744

Updated 3-Aug-15 9:07am

DamithSL

v2

Add a Solution

4 solutions

Solution 3

Since you're using Microsoft SQL Server 2008 R2, another alternative would be to use the ROW_NUMBER function[^]:

SQL

WITH cteSortedValues As
(
  SELECT
    minv_code,
    alert_msg,
    alert_time,
    ROW_NUMBER() OVER (PARTITION BY minv_code ORDER BY alert_time DESC) As RN
  FROM
    @Table
)
SELECT
  minv_code,
  alert_msg,
  alert_time
FROM
  cteSortedValues
WHERE
  RN = 1
;

Demo[^]

Posted 3-Aug-15 10:59am

Richard Deeming

Solution 4

SQL

select a.minv_code,a.alert_msg,a.alert_time from(
select minv_code,alert_msg,alert_time,ROW_NUMBER() over(partition by minv_code order by alert_time desc) as rr from TABLE1123)a
where a.rr=1

Posted 11-Aug-15 1:59am

Member 11899405

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-08-03T08:37:00

Solution 1

Try:

SQL

SELECT m.* From MyTable m
INNER JOIN
   (SELECT minv_code, MAX([date]) AS [date] FROM myTable GROUP by minv_code) g
   ON m.minv_code = g.minv_code AND m.[date] = g.[date]
   ORDER BY minv_code ASC

Posted 3-Aug-15 8:37am

OriginalGriff

DamithSL · Accepted Answer · 2015-08-03T09:25:00

Solution 2

SQL

select minv_code, alert_msg, alert_time
from table1 u1
where alert_time = (select max(alert_time)
                     from table1 u2
                    where u1.minv_code = u2.minv_code)

order by minv_code

DEMO[^]

Posted 3-Aug-15 9:25am

DamithSL