2 
Current State id | val | varchar_id| uid ---------------------- 1 | 1 | A4D NEWID() 1 | 2 | A3G NEWID() 2 | 1 | 7S3 NEWID() 2 | 1 | 43E NEWID() 2 | 2 | 7S3 NEWID() 2 | 2 | 431 NEWID() 3 | 1 | 432 NEWID() 3 | 2 | 43P NEWID() Ideal state id | val | varchar_id| ---------------------- 1 | 1 | A4D NEWID() 1 | 2 | A3G NEWID() 2 | 1 | 7S3 NEWID() 2 | 2 | 7S3 NEWID() 3 | 1 | 432 NEWID() 3 | 2 | 43P NEWID()

Removing of duplicate occurrences of id + val

I have tried (pseudo code below):

SELECT * from table WHERE uid = MAX GROUP BY id, val

Does anyone know of a solution to this/ am I missing something here? I do not mind which of the duplicates are returned.

Also, the version of Sybase I am using does not allow Partition x over x,y functionality.

Improve this question

1 Answer 1

Reset to default
4

Using SQL you can do it this way. Also your where clause isn't what SQL supports.

DECLARE @T TABLE (ID INT, Val INT, V_ID VARCHAR(50), uidd UNIQUEIDENTIFIER) INSERT INTO @T VALUES (1,1,'A4D',NEWID()), (1,2,'A3G',NEWID()), (2,1,'7S3',NEWID()), (2,2,'43E',NEWID()), (2,2,'7S3',NEWID()), (2,2,'431',NEWID()), (3,1,'432',NEWID()), (3,2,'43P',NEWID()) SELECT t.id, t.Val, MAX(V_ID) AS varchar_id, MAX(uidd) FROM @T AS t GROUP BY id, val ORDER BY id, val

This will give you the result

+---+----+-----------+-------------------------------------+ |id |Val |varchar_id |uid | +---+----+-----------+-------------------------------------+ |1 |1 |A4D |5296ACE4-573A-4A7E-882F-516EA8E9DBDD | |1 |2 |A3G |3EE82BEE-8C18-4415-BB3D-110F443409B5 | |2 |1 |7S3 |68DBF7B3-316D-4A8B-B8AD-8825EC83585D | |2 |2 |7S3 |01C54277-7156-47E1-9205-DD577A726196 | |3 |1 |432 |6F53F332-FC9C-4EE1-A3D2-1D0FD002DDAF | |3 |2 |43P |7B532EBD-E6C9-4BE4-B0F7-FCBCB9CE1D61 | +---+----+-----------+-------------------------------------+
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for this Kevin :) The where clause, I typed MAX to save me typing out a sub select for selecting MAX as it is out of scope for the problem, trying to keep the question free of bloating :D
MAX(V_ID), MAX(uidd) - these two MAX can give values from two completely different records (rows). Asker may be surprised, since she (probably) wanted to pick one row, not two values from two rows ;)
@krokodilko I'm a guy :') , also noted, will apply to my data set and configure for what I am after :D

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.