?
Solved

Can Anyone Simplify my SQL Statement?

Posted on 2009-04-09
24
Medium Priority
?
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 9
  • 2
24 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24113602
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
ID: 24113629
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
ID: 24113664
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24113669
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
ID: 24113675
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
ID: 24113739
ok, i'll try it
0
 

Author Comment

by:Fantym
ID: 24113798
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
ID: 24113829
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
ID: 24113857
I 'think' I know what the problem is... gimme a sec and I'll post another one
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 24113877
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
ID: 24113889
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
ID: 24113912
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
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24113927
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
ID: 24113929
well, good night, i'll try that last bit of code tommorow after work, thanks
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24113950
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
ID: 24115525
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
ID: 24118431
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
ID: 24120660
i see, lemme try it later, but i think you may have nailed it.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 24121087
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
ID: 24123134
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 2000 total points
ID: 24123527
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
ID: 24124725
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
ID: 24124806
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
ID: 24125865
ok thanks
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

771 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