Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

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.
0
KazooSoft
Asked:
KazooSoft
1 Solution
 
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
 
Rajkumar GsSoftware 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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now