March 23, 2009 at 5:03 pm
Hey,
following expects to display distinct rows but it is not.. can any body help me out..
TID datatype is integer
SELECT distinct TID, EID, ENAME, EDATE, UID, EISSUCCESSFUL, EREMARKS
FROM HC_ENTRIES WHERE UID=10 ORDER BY EDATE
output:
269Report-Mar 17 2009 11:39AM2009-03-17 11:39:53.373 101done
2010Report-Mar 17 2009 11:40AM2009-03-17 11:40:13.467100undone
2011Report-Mar 17 2009 12:23PM2009-03-17 12:23:13.327101done
3012Report-Mar 18 2009 3:53PM2009-03-18 15:53:29.670101done properly
2113Report-Mar 19 2009 9:31AM2009-03-19 09:31:14.577101done properly
2014Report-Mar 19 2009 9:31AM2009-03-19 09:31:14.577100successfull
March 23, 2009 at 5:05 pm
it seems correct then why no proper result?
March 23, 2009 at 5:10 pm
suhailquadri (3/23/2009)
Hey,following expects to display distinct rows but it is not.. can any body help me out..
TID datatype is integer
SELECT distinct TID, EID, ENAME, EDATE, UID, EISSUCCESSFUL, EREMARKS
FROM HC_ENTRIES WHERE UID=10 ORDER BY EDATE
output:
269Report-Mar 17 2009 11:39AM2009-03-17 11:39:53.373 101done
2010Report-Mar 17 2009 11:40AM2009-03-17 11:40:13.467100undone
2011Report-Mar 17 2009 12:23PM2009-03-17 12:23:13.327101done
3012Report-Mar 18 2009 3:53PM2009-03-18 15:53:29.670101done properly
2113Report-Mar 19 2009 9:31AM2009-03-19 09:31:14.577101done properly
2014Report-Mar 19 2009 9:31AM2009-03-19 09:31:14.577100successfull
We would need the table DDL (CREATE TABLE statement), the sample data that was used to produce the above result set, and what the actual result set should be. From looking at the output, nothing is duplicated, so you have a distinct set of data.
March 23, 2009 at 5:13 pm
Aye, those results are all distinct if only because the timestamp is different in every one.
DISTINCT merges rows were all columns are identical so adding a timestamp will often make it redundant.
Saying that the combination of TID and EID is different for every row stated so just with those 2 columns listed you would expect the same number of rows with a DISTINCT.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
March 23, 2009 at 5:16 pm
I am expecting TID SHOULD BE UNQUIE IN MY RESULTS.. THAT TOO UPDATED DATE..
DEFINATION FOR THIS TABLE IS
EIDintno410 0 no(n/a)(n/a)NULL
ENAMEnvarcharno100 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
EDATEdatetimeno8 no(n/a)(n/a)NULL
UIDintno410 0 no(n/a)(n/a)NULL
TIDintno410 0 no(n/a)(n/a)NULL
EISSUCCESSFULbitno1 no(n/a)(n/a)NULL
EREMARKSnvarcharno100 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
March 23, 2009 at 5:37 pm
IS IT POSSIBLE to get unique TID though we have other records unique? I mean TID should be repeated only one that too with value 20 with updated values?
March 23, 2009 at 5:51 pm
You need to specify how you want to keep TID unique.
Bsically this comes down do a definition of the values in the othe columns to exclude ot limit.
For example only get the latest date, only get the highest EID etc.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
March 23, 2009 at 6:08 pm
As I said before, we need the DDL (CREATE TABLE statement) for the table, sample data for the table (formated as INSERT statements so we may cut, paste, and run the code to load the table), and the expected results based on the sample data.
Please read the first article referenced below in my signatute block regarding how to ask for assistance.
March 23, 2009 at 9:36 pm
Hi,
Use of the DISTINCT, merges rows were all columns are identical (as mentioned in the Lynn Pettis commend) but in OP, the values not identical then why the DISTINCT should be use for this?
ARUN SAS
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply