Solved

Can Anyone Simplify my SQL Statement?

Posted on 2009-04-09
24
202 Views
Last Modified: 2012-05-06
Hello, I'm currently working on a database app, and to make it function the way I wanted it to I had to resort the very large very ugly Query string you see below.  So it is function for me, and what is does exactly is take all items of a certain category from the main table, makes another table and places the subset in there and indexes it, then it displays only x number of items from the resulting indexed table. I'm using this with ASP.NET 2003, and I insert the category and page ranges with code, and it functions. but it's ugly.
CREATE TABLE Temp(PageKey numeric(18,0), "Item #" numeric(18,0),"Short Description" ntext,"Long Description" ntext,"Retail" decimal(18,0), "Category" varchar(50)) 

CREATE TABLE Temp2(PageKey numeric(18,0), "Item #" numeric(18,0),"Short Description" ntext,"Long Description" ntext,"Retail" decimal(18,0), "Category" varchar(50)) 

INSERT INTO Temp (PageKey, "Item #","Short Description","Long Description","Retail", "Category") 

SELECT PageKey, "Item #","Short Description","Long Description","Retail", "Category" FROM Items WHERE Category='''Beautiful_Beasts''' 

INSERT INTO Temp2 (PageKey, "Item #","Short Description","Long Description","Retail", "Category") 

SELECT (SELECT count("Item #") FROM Temp AS x WHERE x."Item #"<y."Item #") AS PageKey, "Item #","Short Description","Long Description","Retail", "Category"  FROM Temp AS y order by PageKey 

SELECT * FROM Temp2 WHERE PageKey BETWEEN 6 AND 12

DROP TABLE "Temp"

DROP TABLE "Temp2"

Open in new window

0
Comment
Question by:Fantym
  • 13
  • 9
  • 2
24 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
I'm still looking at the last statement and will post again if I can help with it, but the first thing you could do to reduce your code would be to use "SELECT INTO" statements.  This would allow you to cut out your create table statements and reduce your insert statements to just the select portion of the statement.
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
OK, I did this quickly and I wouldn't be surprised if I made a syntax mistake, but you should be able to figure out if it doesn't work on the first run...



SELECT (   SELECT count("Item #")
                  FROM Items
                  WHERE "Item #" < "Item #") AS PageKey,
              "Item #",
              "Short Description",
              "Long Description",
              "Retail",
              "Category"
FROM Items WHERE Category='''Beautiful_Beasts'''
ORDER BY PageKey
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Whoops, I did that a little too fast, here's take 2:

SELECT (SELECT count("Item #")
      FROM Items AS x
      WHERE x."Item #"<y."Item #" AND Category='''Beautiful_Beasts''') AS PageKey,
      "other fields",
      "etc..."
FROM Items AS y
WHERE PageKey BETWEEN 6 AND 12
ORDER by PageKey

0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
and omit the "AS PageKey" from the end of that middle WHERE clause.  I was copying and pasting the statements together and accidently forgot to chop it off.
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Sorry, it's late and this site doesn't let me delete comments... Ignore my last comment above, the last SQL statement should work as is.  That statement should replace all your provided code
0
 

Author Comment

by:Fantym
Comment Utility
ok, i'll try it
0
 

Author Comment

by:Fantym
Comment Utility
No Such Luck, Attached is A Screen Shot of What it Returned.

Oh yea, and Page key has values in it covering the whole table.
This is part of a page that displays 6 items at a time in a table, Like a catalog
if you want to see it function, goto http://www.GreeneTech.Info/Products/
it's just a test page at the moement, thanks for all the comments so far.
SELECT (SELECT count("Item #") 

      FROM Items AS x 

      WHERE x."Item #"<y."Item #" AND Category='''Beautiful_Beasts''') AS PageKey, 

      "Item #",

      "Short Description",

      "Long Description",

      "Retail", 

      "Category"

      

FROM Items AS y 

WHERE PageKey BETWEEN 6 AND 12

ORDER by PageKey

Open in new window

sqlResult1.JPG
0
 

Author Comment

by:Fantym
Comment Utility
It's Late, and i'm tired, but I feel like i've been a bit confusing here is how it would look in psudo

Let's assume the database has a key that runs from 1 to the last record (+=1), no repeats.

Each record has a category but not all have the same value for category

Get all records from category Items

now re-number the key in the results from 1 to last record of the results

grab records from the results 6 at a time on a web page  i.e. 1 - 6

if you need the secnond 6 it will get records 7 - 13

Thanks in advance for any help
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
I 'think' I know what the problem is... gimme a sec and I'll post another one
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
If you want to number your items use a rank function

SELECT RANK() OVER (PARTITION BY PAGEKEY ORDER BY PAGEKEY) PageIndex, X.*
FROM Items X
WHERE PageKey BETWEEN 6 AND 12
ORDER by PageKey, PageIndex
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
I think this'll work, but if not your own your own cause I'm about to hit the hay :)
The confusion for me was that you had a derived field called PageKey and an actual field called PageKey.  I put the actual pagekey in the where clause instead of the derived pagekey the first time.  So hopefuly this will do the trick:

SELECT (SELECT count("Item #")
      FROM Items AS x
      WHERE x."Item #"<y."Item #" AND Category='''Beautiful_Beasts''') AS PageKey,
      "Item #",
      "Short Description",
      "Long Description",
      "Retail",
      "Category"
     
FROM Items AS y
WHERE (SELECT count("Item #")
      FROM Items AS x
      WHERE x."Item #"<y."Item #" AND Category='''Beautiful_Beasts''') BETWEEN 6 AND 12
ORDER by PageKey
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
By the way, this will most likely run slower than your original code due to the subqueries.  By using a WITH clause to seperate out the subqueries you might be able to make it work faster, but I'm not sure about that.

There's definitely a better approach though, and if I think of it I'll post it tomorrow :)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Can you do AJAX?  If you really want to do it right ('right' being a matter of opinion, of course), you could pass the result set for an entire category to the client and put it into an array.  Then just javascript to update the display and track which 6 they are on.

The major benefit to this is that they only have one request to the server per category they want to look at and the page updates on their end will be even faster as they traverse a category.

Probably more work than you're interested in doing, but I thought I'd throw it out there.
0
 

Author Comment

by:Fantym
Comment Utility
well, good night, i'll try that last bit of code tommorow after work, thanks
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Oh oh oh, I think I got it :)

Right now you are keeping track of the Page and Category in the URL.  You could also track the page key in the URL.  That way you could greatly simplify your SQL to just look for items that are in the correct category and have a page key greater than what they are currently on.  Then just put "TOP 6" right after "SELECT" and it will give you the next 6.

Basically:
SELECT TOP 6 blah, blah2, blah3 where category = 'whatever' and pagekey > current_pagekey

Obviously, you would then need to change current_pagekey to be whatever the page key for the 6th item is.
0
 

Author Comment

by:Fantym
Comment Utility
that's close, but, page key is not individual to each category. Until the code fills it PageKey Spans Many Categories, and after I get the result containing just the thing from the category I want, it gets renumbered 0-xx, That way i'm also free to jump around
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
That doesn't matter.  Using TOP 6 will just pull the top 6 of whatever your result set is.  As long as you sort by the page key, it doesn't matter whether the values are contiguous or not.  So you obviously will not be able to predetermine what the pagekey for the 6th item is in order to know where to start for the next 6, you'll have to manually find that out and then in your next call only grab values that are above that one.
0
 

Author Comment

by:Fantym
Comment Utility
i see, lemme try it later, but i think you may have nailed it.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
Rank adds a index number to each record, per page

so if you record 18 to 23 from page 6:

SELECT * FROM (
SELECT RANK() OVER (PARTITION BY PAGEKEY ORDER BY PAGEKEY) PageIndex, X.*
FROM Items X
WHERE PageKey BETWEEN 6 AND 12
ORDER by PageKey, PageIndex) A
WHERE A.PageKey = 6 and A.PageIndex >= 18 and A.PageIndex < 24
0
 

Author Comment

by:Fantym
Comment Utility
I've now tried all the solutions and so far, they aren't returning correctly, i'll try a few more things
0
 
LVL 5

Accepted Solution

by:
brandonvmoore earned 500 total points
Comment Utility
What was your issue with saving the page key in the url and using top 6 to pick the next set that are above the last viewed item?  I'm certain that will work, so maybe I can help you figure it out.
0
 

Author Comment

by:Fantym
Comment Utility
the problem arises because page key already has a value, so I need to fill the pagekey after I retrieve just one category, I think I also might be biased toward doing things a certain way, so i'mma give you the points,  cause you've been helpful and i'm sure your way would work also, if I wanted to change a bunch of other things i'm doing
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Haha, ok.  But just to clarify one thing: You wouldn't have to fill the pagekey after retrieving the items.

Let's say the page key values for category X were as follows:
5,6,34,58,85,91,103,105,120,135,136,141,151,199,207

Your first query would look for the 1st 6 values in Category X that were greater than 0:
5,6,34,58,85,91

You would save the last number, 91, to the URL.  I saw that you are already passing a couple other values in the URL so I assume you know how to do this.  You could also save it to a cookie or if you're using sessions save it so a session.  Although the URL is probably best.

The next query would look for the 1st 6 values in Category X that were greater than 91:
103,105,120,135,136,141

See! No renumbering the the pagekey :)
0
 

Author Comment

by:Fantym
Comment Utility
ok thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now