Get Min Records only in sql server

Question

I have table like below, I want min "Id" record if emp_id is duplicate, with out using "correlate query" or "partition over".

id    emp_id    emp_name      phone#        age    
1      101       aaa          12345        25
2      101       aaa          34567        30
3      102       bbb          54325        45

Expected output:

id    emp_id    emp_name    phone#        age 
1      101       aaa        12345         25
3      102       bbb        54325         45

Note: I have done by correlate query

Why can't use you a correlated query or an `OVER` clause? What *have* you tried, as there are 100's (of duplicates) for this question in general on SO already. — Thom A, Aug 13 '19 at 10:52
Possible duplicate of [SQL query to select distinct row with minimum value](https://stackoverflow.com/questions/15291506/sql-query-to-select-distinct-row-with-minimum-value) — Amira Bedhiafi, Aug 13 '19 at 10:53
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) (the most voted up answer has something that works for everything). — Thom A, Aug 13 '19 at 10:54
@Sami - isn't working in case the second row has lower phonenumber or age? — Sebastian Brosch, Aug 13 '19 at 10:54
*with out using "correlate query" or "partition over".* Care to explain why you don't want to use the well-known approach? — Zohar Peled, Aug 13 '19 at 10:58
please post which approach you're trying to implement but not succeeded — DarkRob, Aug 13 '19 at 10:59
The easiest and probably best in terms of query performance way to do this is to use `row_number()`, either with a cte or a derived table. Why don't you want to use that? — Zohar Peled, Aug 13 '19 at 11:01
Seems the OP has no intention of telling us *why* they have set a requirement to not use the standard methods of doing this, or their attempts. :/ The only reason I can imagine the OP can't use `ROW_NUMBER` is because they are using a version of SQL Server that pre dates SQL Server 2005. If that is the case, however, then they should be telling us they are using such outdated technology (and should really be looking at update paths instead). — Thom A, Aug 13 '19 at 11:05

Zohar Peled · Answer 1 · 2019-08-13T11:07:49.253

0

The easiest and probably best in terms of query performance way to do this is to use row_number(), either with a cte or a derived table.
If you want to do it without using row_number() or a correlated subquery that mimics it, you could do something like this:

SELECT id, emp_id, emp_name, phone#, age
FROM YourTable 
INNER JOIN (
    SELECT MIN(id) As Minid
    FROM YourTable
    GROUP BY emp_id
) As MinIdPerEmp
    ON id = MinId

Just for clarity, a row_number() query would look like this:

SELECT id, emp_id, emp_name, phone#, age
FROM 
(
    SELECT id, emp_id, emp_name, phone#, age,
           ROW_NUMBER() OVER(PARTITION BY emp_id ORDER BY id) As rn
    FROM YourTable 
) As T
WHERE rn = 1

edited Aug 13 '19 at 11:07

answered Aug 13 '19 at 11:04

Zohar Peled

79,642
10
69
121

This does, however, go against the OP's (unjustified) requirement of *'with out using "correlate query" or "partition over".'*. Hopefully they'll give us understand of why they don't want to use them. otherwise they might as wekll be asking something to paint a wall blue, but they aren't allow to use blue paint. >_ – Thom A Aug 13 '19 at 11:06
@Larnu last I've checked, a derived table is not a correlated subquey. S derived table based solution is therefor not using "currelate query" (Unless the OP doesn't know the difference between the two, of course) – Zohar Peled Aug 13 '19 at 11:09
True enough; in fairness I assumed that by "correlated query" they meant sub-query. – Thom A Aug 13 '19 at 11:10

Ilyes · Answer 2 · 2019-08-13T11:11:25.453

You could use

SELECT T.*
FROM T JOIN 
(
  SELECT MIN(ID) ID,
         MIN(Age) Age,
         Emp_Id
  FROM T
  GROUP BY Emp_Id
) TT ON T.Id = TT.Id AND T.Age = TT.Age;

OR

SELECT 
id, emp_id, emp_name,   phone,  age
FROM 
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Emp_Id ORDER BY Id) RN
  FROM T
) TT
WHERE RN = 1;

Or even a LEFT JOIN as

SELECT T.*
FROM T LEFT JOIN T TT
ON T.Emp_Id = TT.Emp_Id
   AND
   T.Age > TT.Age
WHERE TT.Id IS NULL;

Online Demo

score 0 · Answer 3 · answered Aug 13 '19 at 11:19

With NOT EXISTS:

select t.* from tablename t
where not exists (
  select 1 from tablename
  where emp_id = t.emp_id and id < t.id
)

See the demo.
Results:

> id | emp_id | emp_name | phone | age
> -: | -----: | :------- | ----: | --:
>  1 |    101 | aaa      | 12345 |  25
>  3 |    102 | bbb      | 54325 |  45

score 0 · Answer 4 · answered Aug 13 '19 at 11:25

0

SELECT t1.id
     , t1.emp_id
     , t1.emp_name
     , t1.phone#
     , t1.age
FROM table_name t1 
LEFT JOIN table_name t2
ON t1.Emp_Id = t2.Emp_Id
AND t1.Id > t2.Id
WHERE 
    t2.Id IS NULL;

answered Aug 13 '19 at 11:25

Mahesh Waghmare

726
9
27

Get Min Records only in sql server

4 Answers4