0

I had some confusion regarding distinct keyword...

For example Table test has 5 columns a b c d e, and column a has repeated value, and I want to use distinct keyword to get distinct values while also fetching data from other columns

eg. select distinct * from test (I know the syntax is wrong here, I have written the query to help people get the idea of what I'm trying to do here)

Can someone help here?

///edit

ID  DEGREE  AWARDED ACADEMIC_PERIOD
147 GR           MS 2022
147 UG           BS 2020
170 GR           MA 2023
170 UG           BA 2018

Expected output is

ID  DEGREE  AWARDED ACADEMIC_PERIOD
147 GR           MS 2022
170 GR           MA 2023

found this data on dbastackexchange itself

9
  • Which record, from the records with multiple values for a, do you want in the final results? (It would be impossible to tell, when looking at this example: dbfiddle.uk/p0F8tAyB )
    – Luuk
    Oct 3, 2023 at 11:32
  • Please consider following these suggestions.
    – mustaccio
    Oct 3, 2023 at 11:48
  • @Luuk, from column a, I only want the distinct values, not anything else, while also getting the values from the other columns - eg select * or select distinct a , b, c, d Oct 3, 2023 at 12:00
  • @datascinalyst It would be best if you followed mustaccio's comment, and included some sample data and an example of the output you expect. What you're asking for is too ambiguous to describe with just words.
    – J.D.
    Oct 3, 2023 at 12:21
  • @J.D., sure, I'll add some data I actually found here on dbastackexchange. I have edited it to the main question Oct 3, 2023 at 12:36

1 Answer 1

0
SELECT
  ID,
  DEGREE,
  AWARDED,
  ACADEMIC_PERIOD
FROM test
WHERE (ID, ACADEMIC_PERIOD) in (
  SELECT
   ID,
   MAX(ACADEMIC_PERIOD)
   FROM test
   GROUP BY ID);

see: DBFIDDLE

The subquery selects the ID, and the needed/wanted ACADEMIC_PERIOD, so the correct records can be filtered.

EDIT: Another approach (same results)

SELECT 
  ID,
  DEGREE,
  AWARDED,
  ACADEMIC_PERIOD
FROM (
  SELECT
    ID,
    DEGREE,
    AWARDED,
    ACADEMIC_PERIOD,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ACADEMIC_PERIOD desc) as R
  FROM test) x
WHERE R=1
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.