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 |
gabriel.k
Starting Member
6 Posts |
Posted - 2007-04-11 : 17:00:58
|
Hi,I'm trying to get the position of a single value in the query results.like this:select * from tPerson order by ds_aliasresults inid_person | ds_name | ds_alias 15 | mark | AA 20 | john | AA 5 | mike | BB 8 | thomas | JK 2 | mike | MM as you can see, I can have registries with the same name and with the same alias, there's no restrictionand I'm trying to find out how many registries there are before the second "mike", wich would be 3.. so it's the 4th element, get it?I have no idea how to...Thank in advance |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-11 : 17:38:08
|
[code]-- Prepare sample dataDECLARE @Sample TABLE (id_person INT, ds_name VARCHAR(10), ds_alias VARCHAR(10))INSERT @SampleSELECT 15, 'mark', 'AA' union allSELECT 20, 'john', 'AA' union allSELECT 5, 'mike', 'BB' union allSELECT 8, 'thomas', 'JK' union allSELECT 2, 'mike', 'MM'-- Stage some dataDECLARE @Name VARCHAR(20)SELECT @Name = ds_nameFROM @SampleWHERE ds_alias = 'mm'-- Show the expected outputSELECT COUNT(*) + 1 AS PositionFROM @SampleWHERE ds_alias <= 'mm' AND ds_name <> @Name[/code]Peter LarssonHelsingborg, Sweden |
|
|
gabriel.k
Starting Member
6 Posts |
Posted - 2007-04-12 : 14:13:12
|
I had tried using count, but it didn't work...Two registries can have the same alias, so how can I make the count stop at the element I want so I can get it's position?Heres an example with some real data (values are in portuguese)SELECT *FROM clientsWHERE ds_name <= 'a quatro' and ds_alias <> ''ORDER BY ds_alias---------------------------------cd_client | ds_name | ds_alias26 | BANCO SANTOS NEVES | BCO STO NEVES1857 | GIOVANNA GONÇALVES DIAS | 100% EVENTOS1973 | VITÓRIA ENTRETENIMENTO LTDA | 100% VIDEO240 | 3 PONTOS PROPAGANDA LTDA | 3 PONTOS PROPAGANDA2 | A EDICAO LIVRARIA E DIST. LTDA | A EDICAO LIVRARIA538 | S/A A GAZETA | A GAZETA (JORNAL)3 | A GAZETA DO ESPIRITO SANTO RADIO E TV LTDA | A GAZETA (RADIO / TV)340 | A4 PUBLICIDADE E MARKETING LTDA | A QUATRO2463 | SDC/QUATRO COMUNICAÇÃO E MARKETING LTDA | A QUATRO There are two clients with the 'A QUATRO' alias, the problem is that if I do a count of all elements with an alias <= 'A QUATRO' it'll return 9.How can I discriminate wich one I want, so I can get it's position?(sorry for the really awful english) |
|
|
X002548
Not Just a Number
15586 Posts |
|
gabriel.k
Starting Member
6 Posts |
Posted - 2007-04-12 : 15:27:36
|
I did it! Yay It's ugly as sin, though....I realised that I was trying to get the number of elements with a "smaller" alias plus all the elements with the same alias but with smaler id's (since the id is the secondary ordering value), right?Soselect cd_client (this is the id), ds_name, ds_alias, (select (count(c2.cd_client) + (select count(c3.cd_client) from clients c3 where c3.ds_alias <> '' and c3.ds_alias = c1.ds_alias and c3.cd_client <= c1.cd_client)) from clients c2 where c2.ds_alias <> '' and c2.ds_alias < c1.ds_alias) as Posfrom clients c1order by c1.ds_name So i get the code, name and alias ordered by name, but with the position of each element in a result ordered by alias....It is incredbly ugly... but I couldn't find a way to make a conditional WHERE, so i could compare id's only if the elements had equal aliases...is there a way this can be done? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-12 : 15:38:25
|
What you are trying to achieve do not match the original description!Anyhow... Try thisSELECT s1.cd_client, s1.ds_name, s1.ds_alias, (SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.ds_alias < s1.ds_alias) + 1 + (SELECT COUNT(*) FROM @Sample AS s3 WHERE s3.ds_alias = s1.ds_alias AND s3.cd_client < s1.cd_client) AS PosFROM @Sample AS s1ORDER BY 4 Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|