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.
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 |
+---+----+-----------+-------------------------------------+