Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

How to select unique record based on 1 column

Dhiraj Kr_ Gupta March 13, 2024

Name ID Age

John.  12.   14

Snow.   13.    16

John.   12.     18

Ken.     14.      19

Snow.   13.     17

 

I want to get only those records having distinct I'd  order by age

Output

John.  12.   14

Snow.   13.    16

 

 

1 answer

1 accepted

2 votes
Answer accepted
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 13, 2024

Hi @Dhiraj Kr_ Gupta ,

You may try to "play" with the following SQL query:

Wed 10-1.png

SELECT *,
MIN('Age') AS 'Minimum Age'
FROM T*
GROUP BY 'ID', 'Name'

Wed 10-2.png

Hope it helps your case.

 

Dhiraj Kr_ Gupta March 13, 2024

Thanks for your reply

But I have  same date for two records instead of Age 

How can I assign rank in that case.

I need to add a time stamp for each record which is unique for each record 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 13, 2024

You may add only current time stamp or date for the whole column with the help of the Table Transformer macro (automatically, I mean). Later this current date can be compared to other dates (to calculate the difference between now and due date, for example).

But if to talk about ranking, maybe standard row autonumbering will suit you? If you add new entries only in the end of your table (you don't add rows randomly between existing rows), then the most recent entries will have the biggest row numbers. You'll use the MAX function instead of MIN and the row numbers appear automatically when you add a new table row. 

Dhiraj Kr_ Gupta March 13, 2024

What if we have same dates for two records

Name    Date

John     03-13-2024

Snow    03-13- 2024

These two records are added at different time in the table

Is there any way if I get the time also or Latest among them

These records are coming from Confluence form responses

 

 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 14, 2024

Hi @Dhiraj Kr_ Gupta ,

If you have dates in your source table, you may use the following query:

Thu 3-1.png

SELECT 'ID', LAST('Name'), LAST('Date')
FROM T*
GROUP BY 'ID'

Here we get the last entry for each unique ID.

If the dates are equal (as you can see in the source table, John 2 and John 3 have the same date), the last entry will be taken no matter what.

So, you'll see John 3 in the result table:

Thu 3-2.png

Hope it helps your case.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events