Complex logic -- use GROUP BY? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Complex logic -- use GROUP BY?

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-13 : 10:20:20

Hi,

I am getting real estate pictures from a database which have a certain order in which they are to be posted (ex: A, B, C, D, E, F), and all these pictures are related to one property. There are 12 possible pictures in the database for any given property, but only 6 are allowed to be posted.

I am unsure how to handle situations where say "property NY" has three pictures, and they are picture 7 , picture 9 and picture 12 -- and they would have to be posted as picture A, picture B, and picture C in sequencial order?

So any pictures that are extracted, all have to be posted in some sequencial order from A - F, depending on how many pictures are available for that property. But there are situations where a property has several pictures that don't start in a nice sequencial order (pic 1, pic 2, pic 3, etc..) The property has pictures that are say pic 3, pic 5, pic 7, but would still have to be displayed as A, B, and C.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-13 : 10:39:35

Post some DDL and expected results..

From what you have said, a simple order by will post them in the correct order..

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-13 : 10:48:46

example;


SELECT case photo_sequence
         when 1 then 'A'
         when 2 then 'B'
         when 3 then 'C'
         when 4 then 'D'
         when 5 then 'E'
         when 6 then 'F'
       end 
FROM photos

but unsure how to handle situation where say the photo is not 1-6, but say 7, 9, and 10 and I still want to be able to display say:

- 7 as A
- 9 as B
- 10 as C

In another words, if a given property has 4 pictures, and they are pic4, pic5, pic6, pic7, I want to display that as:

- 4 as A
- 5 as B
- 6 as C
- 7 as D

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-13 : 11:14:52

Where does the A,B and C come into play? Why is this label important? In your code that displays these pictures, can you just increment an integer and return the "Picture #" with something simple like:

Char(64 + PicNo)

Which returns "A" for the PicNo 1, "B" for picNo 2, and so on. You would simply set PicNo to "1" for the first picture, and increment by 1 as each is read in from the database. All you are doing is applying a label, which can be done at the presentation layer, there should be no need to do this calculation in SQL.

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-13 : 11:17:07

I am designing an automated ftp data feed to a remote site which requires that spec (A, B, C, etc...). The picture display order (1, 2, 3, etc) needs to be aliased to (A, B, C, etc...)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-13 : 11:18:42

No really...

quote:
Post some DDL and expected results..

The approach is going to depend on how the data is stored. Script out the DDL (i.e. CREATE TABLE dbo.MyTable (Col1 INT, Col2 VARCHAR(25)...etc) and some DML (i.e. INSERT INTO dbo.MyTable (Col1, Col2) SELECT 1, 'Blah'...etc) and an indication of the results you expect, and you should get an answer!

Mark

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-13 : 11:59:02

ok...I'll try

The thing is I'm creating a temp table and then merging that with another table to get needed results.

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-13 : 12:20:40

[code]

CREATE TABLE Numbers (
[PROPID] INT NOT NULL,
[DISPLAY_ORDER] VARCHAR(50) NOT NULL
)

INSERT INTO Numbers (PROPID, DISPLAY_ORDER)
VALUES ('1234', '1', etc...)

SELECT PROPERTIES.PROPID,
CASE PHOTOS.DISPLAY_ORDER
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
WHEN 4 THEN 'D'
WHEN 5 THEN 'E'
WHEN 6 THEN 'F'
END

FROM Numbers
JOIN PROPERTIES
ON Number.PROPID = PROPERTIES.PROPID
JOIN PHOTOS
ON PROPERTIES.PROP_ID = PHOTOS.PROP_ID

[/code]

Hope this helps.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-14 : 05:33:30

It's kind of ugly, but you could use a correlated subquery to generate a contiguous order within PROPID and then apply the Doctor's approach:

SELECT 
    n.PROPID, 
    CHAR(64+(SELECT 
                 COUNT(*)
             FROM 
                 dbo.Numbers AS n2 
             WHERE 
                 n2.PROPID = n.PROPID 
                 AND n2.DISPLAY_ORDER <= n.DISPLAY_ORDER
             )
         ) 	
FROM 
    Numbers AS n
    JOIN PROPERTIES AS p
    ON n.PROPID = P.PROPID
    JOIN PHOTOS AS ph
    ON P.PROP_ID = ph.PROP_ID

Mark

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-04-03 : 10:36:50

quote:
Originally posted by jsmith8858

Where does the A,B and C come into play? Why is this label important? In your code that displays these pictures, can you just increment an integer and return the "Picture #" with something simple like:

Char(64 + PicNo)

Which returns "A" for the PicNo 1, "B" for picNo 2, and so on. You would simply set PicNo to "1" for the first picture, and increment by 1 as each is read in from the database. All you are doing is applying a label, which can be done at the presentation layer, there should be no need to do this calculation in SQL.

All you are doing is applying a label, which can be done at the presentation layer, there should be no need to do this calculation in SQL.

I don't quite understand what the part in italics means. Also to increment a counter, could a WHILE loop be used in a SELECT statement?

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-04-04 : 09:58:04

To increment a counter, could a WHILE loop be used in a SELECT statement?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-04-05 : 05:48:21

Have you tried my suggestion?

Mark

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-04-05 : 07:41:16

I did try your suggestion, but due to my inexperience with t-sql, I believe I may have coded it wrongly. Does the CHAR code have to be associated with a column (as I've put in bold)? I guess I don't quit understand where the value retrieved by CHAR function will be used?


SELECT 
    n.PROPID, 
    CHAR(64+(SELECT 
                 COUNT(*)
             FROM 
                 dbo.Numbers AS n2 
             WHERE 
                 n2.PROPID = n.PROPID 
                 AND n2.DISPLAY_ORDER <= n.DISPLAY_ORDER
             )
         )as DISPLAY_ORDER 	
FROM 
    Numbers AS n
    JOIN PROPERTIES AS p
    ON n.PROPID = P.PROPID
    JOIN PHOTOS AS ph
    ON P.PROP_ID = ph.PROP_ID

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-04-06 : 12:25:38

Yeah - that's right.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources