By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,450 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Do not want to display record with same ID

P: 50
Hi everyone, i have a database with the information like:

id transId transType
1001 3001 1
1002 3002 1
1003 3002 0
1004 3005 1

1) 1001, 1002, 1003, 1004 is primary key

2) 3001, 3002 , 3003 is the order's invoice no.

3) 1,0 under trans type is their type: 1, is sales, 0 is cancel order

i wish to get this information from database, to display them at report
but i only want to display 1001, and 1004, meanwhile 1002, and 1003 to be excluded since thier transId is same (a customer make an order, and cancel by tat day so i don't want to display "zero result" record)


Can anyone help me to use sql to get a list from database ???which i don't want those 2 records to be displayed?

Many thanks...
Mar 18 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
Does this help, it covers the scenario you present
Expand|Select|Wrap|Line Numbers
  1. SELECT Min(ID) as ID,TransID,max(TransType) as TransType
  2. FROM YourTable
  3. GROUP BY TRANSID
  4. HAVING count(ID)=1
  5.  
Mar 18 '08 #2

P: 50
Dear Delerna,

Wow , the codes works exactly the way i want. However, i am confuse that why shoud we select the Min(ID) ? Can i have your further explanations?

Thank you very much
Mar 18 '08 #3

Delerna
Expert 100+
P: 1,134
Dear Delerna,
why shoud we select the Min(ID) ? Can i have your further explanations?
Sure
This is an agregate query and as such every field in the selection must be agregated in some way, otherwise you get an error.
So min is just a way for me to include the ID in the selection without getting that error.
For this particular field in this particular query i could just as well have used max or first or last in order to provide the aggregate function necessary in order to include it in the selection list.

I hope that makes sense
Mar 18 '08 #4

P: 50
Sure
This is an agregate query and as such every field in the selection must be agregated in some way, otherwise you get an error.
So min is just a way for me to include the ID in the selection without getting that error.
For this particular field in this particular query i could just as well have used max or first or last in order to provide the aggregate function necessary in order to include it in the selection list.

I hope that makes sense

Oops, that's the way it works.. Many thanks again! :p
Mar 18 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.