MSSQL Distinct, help

Hey all,

I have an issue I have 2 tables with about 20,000 rows in. It contains name, email pins etc. Below see my SQL to retrieve all of the data.

What I need is a way of DISTINCTLY selecting the results but take the one with the highest X_ID.

SELECT x_id, name, email, primarypin
FROM cas_user_ext, cat_validation
WHERE cas_user_ext.x_id = cat_validation.id

This will return for example.

X_ID, name, email, primarypin
11, SChurch, SChurch@dsa.com, 115545544
12, SChurch, Schurch@dsa.com, 151516165

I would like to select the result with ID 12.
LVL 4
KazooSoftAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Try this
Raj
SELECT x_id, name, email, primarypin FROM
(
	SELECT x_id, name, email, primarypin
		, row_number() over (partition by [name], email order by X_ID desc) rowno 
	FROM cas_user_ext, cat_validation
	WHERE cas_user_ext.x_id = cat_validation.id
) A
WHERE A.ROWNO = 1

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might want to read this article:http://www.experts-exchange.com/A_3203.html
0
 
Priya PerumpilavilSoftware EngineerCommented:
try this
SELECT max(x_id), max(name), max(email), max(primarypin)
FROM cas_user_ext, cat_validation
WHERE cas_user_ext.x_id = cat_validation.id
group by name
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Below...

HTH
SELECT distinct name, email, primarypin, max(x_id) as highest_x_id
FROM cas_user_ext, cat_validation
WHERE cas_user_ext.x_id = cat_validation.id
group by name, email, primarypin

Open in new window

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Sorry I misread the question..pls try...
select distinct
A.name, A.email, A.primarypin, A.x_id
from
(
select name, email, primarypin, x_id
FROM cas_user_ext, cat_validation
WHERE cas_user_ext.x_id = cat_validation.id
) A
inner join
(
SELECT distinct name, email, primarypin, max(x_id) as highest_x_id
FROM cas_user_ext, cat_validation
WHERE cas_user_ext.x_id = cat_validation.id
group by name, email, primarypin
) B
on A.x_id = B.highest_x_id

Open in new window

0
 
Priya PerumpilavilSoftware EngineerCommented:
try this
SELECT max(x_id), name, max(email), max(primarypin)
FROM cas_user_ext, cat_validation
WHERE cas_user_ext.x_id = cat_validation.id
group by name

0
 
Gururaj BadamCommented:
This is what you're looking for. I'm doing a self join to get the max record and then return the pertaining columns

SELECT a.x_id, a.name, a.email, a.primarypin
FROM cas_user_ext a, cas_user_ext b, cat_validation
WHERE b.x_id = cat_validation.id and max(b.x_id) = a.x_id
0
All Courses

From novice to tech pro — start learning today.