A bit complex question - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A bit complex question

Author

Topic

shalti
Starting Member

4 Posts

Posted - 2005-08-09 : 10:11:14

Hey people,

I'm writing a small program that works with SQL Server and I've been trying to create this query for quite a while.
The queries job is to search two fields in a table (always the same two fields in the same table) with two given values and give matches sorted by relevancy. I have a specification on the relevancy level, for example:
A record with the values in the 2 fields exactly matching my 2 values is in relevancy level 1 (highest).
A record with the first field set to NULL and the second field exactly matching the second value I'm searching is in relevancy level 2.
A record where the first field can be anything and the second field exactly matches is relevancy level 3.
etc. (there are more relevancy levels)

What I tried so far is creating a SELECT DISTINCT with a subquery that has UNIONs and creating another column that holds the relevancy level and sort by it, but I had alot of problems with the sorting and duplicated records.
Also I put the result in a DataGrid so I don't want any unneccesary columns.

If you have an idea on how I can do it, it will be great...
Thanks in advance!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-09 : 10:37:38

Follow the hint link below....the req is a little foggy

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-09 : 10:49:42

use a union all to get rid of the matches
and you can easily sort like:
select * from
(select 1 as Relevancy, ...
union all
select 2 as Relevancy, ...
union all
select 3 as Relevancy, ...) t
order by Relevancy

Go with the flow & have fun! Else fight the flow

shalti
Starting Member

4 Posts

Posted - 2005-08-09 : 10:53:44

I don't think writing any DML code will make the question more clear, it will just make it more fogy.
I'll try to make it more clear:

I have a table, with a few fields.

My query is suppose to search the table records by only 2 fields. The rest of the fields have nothing to do with this query, but in the end I need them to be shown in the output.

The search will be as I stated in my original message.

I need the results to be sorted by relevancy as I stated in the original message

That's it...
Ask me if something is not clear.

shalti
Starting Member

4 Posts

Posted - 2005-08-09 : 11:01:33

Hey spirit1, thanks for the quick answer.
I tried your code but I'm still getting duplicated results. I need every record to appear only once in the result of the query.
I'll write the code I wrote:


SELECT * FROM (
    SELECT 1 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Prefix] = 'PX' AND [P/N Suffix] = 'ABCD'
    UNION ALL
    SELECT 2 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Prefix] IS NULL AND [P/N Suffix] = 'ABCD'
    UNION ALL
    SELECT 3 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Suffix] = 'ABCD'
    ) t
ORDER BY Relevancy

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-09 : 11:06:16

that's because of "1 AS Relevancy" etc...

uhh... i see your problem now... hmmm.... will have to think about this...

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-09 : 11:44:42

I did some ranking and searching for multiple predicates a while back....kinda the same thing

http://weblogs.sqlteam.com/brettk/archive/2004/05/05/1312.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-10 : 08:06:50

SELECT * FROM (
SELECT 1 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Prefix] = 'PX' AND [P/N Suffix] = 'ABCD'
UNION ALL
SELECT 2 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Prefix] IS NULL AND [P/N Suffix] = 'ABCD'
UNION ALL
SELECT 3 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Suffix] = 'ABCD' AND [P/N Prefix] <> 'PX' AND [P/N Prefix] IS NOT NULL
) t
ORDER BY Relevancy

Kapil Arya

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 08:26:35

quote:
Originally posted by shalti

Hey spirit1, thanks for the quick answer.
I tried your code but I'm still getting duplicated results. I need every record to appear only once in the result of the query.
I'll write the code I wrote:

SELECT * FROM (
SELECT 1 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Prefix] = 'PX' AND [P/N Suffix] = 'ABCD'
UNION ALL
SELECT 2 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Prefix] IS NULL AND [P/N Suffix] = 'ABCD'
UNION ALL
SELECT 3 AS Relevancy, [P/N Prefix], [P/N Suffix] FROM tRFQCus WHERE [P/N Suffix] = 'ABCD'
) t
ORDER BY Relevancy

Why are you getting duplicates?!?! You can't be if that is all of your code... You are specifying a where clause in each part of the union that qualifies the same column to different explicit values in each part. Therefore... no duplicate should be possible.

What else are you doing?

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-10 : 08:37:43

are you sure corey?
this [P/N Suffix] = 'ABCD' is in all three where's... so basicaly the first ones are just undersets of the 3rd one.
no?

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 08:40:57

my bad... field names look too much alike...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 08:43:18

and this for kapilarya's suggestion:

isnull([P/N Prefix],'PX') <> 'PX'
instead of
[P/N Prefix] <> 'PX' AND [P/N Prefix] IS NOT NULL

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

shalti
Starting Member

4 Posts

Posted - 2005-08-11 : 06:21:52

Hey all,

Thanks for all the responses it helped me alot.
I finally got it (using CASE), here's the code:


SELECT DISTINCT Relevancy, [P/N Prefix], [P/N Suffix] FROM (
  SELECT *, Relevancy = CASE 
    WHEN ( [P/N Prefix] = 'PX' AND [P/N Suffix] = 'ABCD' ) THEN 1 
    WHEN ( [P/N Prefix] IS NULL AND [P/N Suffix] = 'ABCD' ) THEN 2 
    WHEN ( [P/N Suffix] = 'ABCD' ) THEN 3
  END 
  FROM tRFQCus 
) t
WHERE Relevancy IS NOT NULL 
ORDER BY Relevancy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources