| Author |
Topic |
|
fonzie
Starting Member
31 Posts |
 Posted - 2005-12-30 : 07:58:14
|
| If I want to show entire records with just the price being distinct, how would I do that. Seems like all that I list would be distinct as well. Thanks! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-30 : 08:00:01
|
| Distinct will give distinct records if rows are duplicatedPost some sample data and the result you wantMadhivananFailing to plan is Planning to fail |
 |
|
|
fonzie
Starting Member
31 Posts |
Posted - 2005-12-30 : 08:11:06
|
| I have hundreds of records fields are: item,location, price. Some of the prices may be the same but the items can be different. For example an OPTICAL FILTER A may have a price of $355.06 and an OPTICAL FILTER B may also have a price of $355.06. Would selecting distinct cause the price to show up twice? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-30 : 08:42:31
|
| Where do you want to show the distinct price?If you use Reports, then make use of Suppress If duplicated or equivalent featureMadhivananFailing to plan is Planning to fail |
|
|
|
fonzie
Starting Member
31 Posts |
Posted - 2005-12-30 : 08:45:05
|
| I just want to implement a usual SELECT statement, whatever duplicated price shows up first chronlogically, gets outputted along with rest of the record. Is this possible? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-30 : 08:51:25
|
| This type of formation should be done in Presentation layerWhat is the purpose of showing this in Query Analyser?MadhivananFailing to plan is Planning to fail |
|
|
|
fonzie
Starting Member
31 Posts |
Posted - 2005-12-30 : 08:59:50
|
| JUST to see if it can be done, to be displayed on asp web. Not sure what the presentation layer is? But when you supress a duplicate row, are we talking about the whole row or just the price? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
fonzie
Starting Member
31 Posts |
Posted - 2005-12-30 : 10:07:46
|
| That blog example was complex. I did read on a web search the following:"DISTINCT Supress duplicate rows - display only the unique values.Duplicate rows have matching values across every column (or expression) in the select_list."So if I "select distinct price from mytable" I may miss getting a row of another item with the same price. Is this correct?IOT, does "select distinct price from mytable" return the same rows as"select distinct price,item from mytable" ?sorry for being so bothersome with this. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-30 : 13:00:34
|
quote:
Originally posted by fonzie
Not sure what the presentation layer is?
It is the front end or the application which is displaying in Client's machine.Eg: When u want to show the results of a query, u don't give that query to the client asking him to run in Query Analyzer. U have to provide with an application (written in ASP or JAVA or VB or C# or some such) or a report written using a tool like Crystal Reports etc.Whatever u need to display can be done more easily in that presentation layer, rather than splitting hair to find a Query to get ur desired results, if the requirement is such complicated.U can get the necessary records by a query and format the presentation as u need.Good if u can put just a few records and how those should appear (Sorry, I couldn't understand ur requirement) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-02 : 02:05:20
|
| Where do you want to show the suppressed data?MadhivananFailing to plan is Planning to fail |
|
|
|
fonzie
Starting Member
31 Posts |
Posted - 2006-01-04 : 10:54:33
|
| I just hoped to run it in the analyser |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-05 : 01:52:37
|
quote:
Originally posted by fonzie
I just hoped to run it in the analyser
Why?Do you then export data to files?MadhivananFailing to plan is Planning to fail |
|
|
|
fonzie
Starting Member
31 Posts |
Posted - 2006-01-05 : 07:29:39
|
| I just want to know if it's possible as an SQL exercise to gather specific data. In a scenario where for example an OPTICAL FILTER A has a price of $355.06 and a record with OPTICAL FILTER B also has a price of $355.06. Would selecting ~distinct~ cause one of the records not to show up in the following statement? SELECT DISTINCT PRICE FROM MYTABLE ? Which record gets selected, the first one chronologically? |
|
|
|
|
Select using distinct clause