How to select unique record based on 1 column

[Dhiraj Kr_ Gupta]

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

[Stiltsoft support · Accepted solution]

Hi @Dhiraj Kr_ Gupta ,

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

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

Hope it helps your case.

[Dhiraj Kr_ Gupta]

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]

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]

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]

Hi @Dhiraj Kr_ Gupta ,

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

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:

Hope it helps your case.