Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jd-
Starting Member
3 Posts |
 Posted - 2004-09-19 : 03:00:19
|
| Hi, Q1.i want to do a "select distinct member, amt from tbl"but my idea is to select distinct members with their corresponding non distinct amts. but the statement above returns distinct (members & amts)how can i solve this ?Q2. is there a query which can do something like"select bid_amt from tbl1, (select quantity from tbl2 where id=2) x limit x.quantity" ? Thanks |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-09-19 : 05:51:45
|
| To q1, DISTINCT works only on rows, and not individual columns. Think about it, if you have one distinct member, and five amounts, what would have in the member column for those four amounts? If you want them blank (like may be required for reports), this becomes a formatting issue, better do it at the front-end. What's wrong with repeating values in one column as long as the row is distinct?q2, can you try and explain what you want from that query? There is no LIMIT clause in SQL server (guess you picked it up from mySql).OS |
 |
|
|
jd-
Starting Member
3 Posts |
Posted - 2004-09-19 : 06:25:28
|
| Hi, Q1) oh i understand now. thanks. Q2) yes. im using mysql. i want to put a variable in limit clause where this variable is taken from a record field in another tableThanks |
|
|
|
jd-
Starting Member
3 Posts |
Posted - 2004-09-19 : 06:33:31
|
| Hi for Q1, im thinking of another solution for a table like thisname amtjohn 10john 15sam 20harry 13harry 17select from tbl group by name returns mename amtjohn 10sam 20harry 13i.e the lower amt with the distinct name. how can i get it to do the same thing but return the higher amt instead? i.ename amtjohn 15sam 20harry 17Thanks |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-09-19 : 07:58:08
|
This is an MS SQL Server forum, we might not be able to help you very well with mysql queries. Try the forums over at www.dbforums.com, they even run the site on mysql  There are some problems with the way mySQL handles certain queries...take a look at the example you gave: select from tbl group by name From any way you look at it, there is simply not enough information for a SQL parsing engine to determine what you want from that query. Is this a complete statement...does this run on mysql as it is? in that case, mysql has simply made an assumption that you need the first record, but SQL Server would have choked at this statement. It is always safe to specify exactly what you, so that you don't rely on default or undocumented behaviour that could change later on. the query you are looking for is:SELECT name, max(amount) FROM table GROUP BY name OS |
|
|
|
|
|
|
|
|
2 queries