Solved

Can Anyone Simplify my SQL Statement?

Posted on 2009-04-09
24
209 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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 37

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 37

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 500 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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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