|
Here's a problem I've come up against on a number of occasions and I have so far failed to come up with an elegant query to deal with it. I feel the GROUP BY clause might have to come into play but I don't really know how.
Let's say I have a table called Orders containing information on orders received from customers. The table has the following columns:
CustomerID (INT)<br />
OrderPlaced (DATETIME)<br />
ProductID (INT)<br />
AmountOrdered (INT)
So I want to write a query that will show me the information from this table on the first order for a specific product (let's say id 123) received by every customer.
The following query will get me almost there:
SELECT CustomerID, MIN(OrderPlaced)
FROM Orders
WHERE ProductID = 123
It only gets me almost there though because notice how the AmountOrdered field is missing from the query and unless it's part of an aggregate function I can't add it. I can add it with a subquery but that is where the query loses its elegance rapidly, especially considering that the real world tables I'm dealing with usually have more than just one or two extra columns.
Any ideas?
|
|
|
|
|
Almost all your solutions using an aggregate will require a sub query, there is nothing inelegant about using one!
You can also look into ROW_NUMBER and PARTITION.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks. I've been shying away from Over(), ROW_NUMBER and Partition By because they're rather Microsoft specific.
I agree, generally there's nothing inelegant about using a subquery but in this particular case it is anything but elegant.
Consider the table in my example and let's say there are a few more columns:
ContractID (INT)<br />
OrderSource (INT)<br />
AgentID (INT)<br />
ProvinceID (INT)<br />
DeliveryMethod (INT)
So, in order to get what I want, using GROUP BY and subqueries, it would end up looking something like this:
SELECT
CustomerID,
MIN(OrderPlaced),
FROM Orders
WHERE ProductID = 123
GROUP BY CustomerID
I'm sure you'll agree that the above query is pretty much the antithesis of elegance.
modified 27-May-13 4:18am.
|
|
|
|
|
No no no you want the ID of the record for each customers earliest order (I hope) so first create a query that gets you that order ID, probably add the orderID to the above query.
Make sure the IDs are the expected results before moving on to the next stage.
Now wrap that query in a
Select * from Orders
where orderID in (Your innerquery)
or if there are multiple fields in your inner query use a join
Select *
From Orders
inner join (Yourinnerquery) X on X.orderid = orders.orderid
You can make the inner query as complex as required but it should return the minimum required to join to your field table (Orders presumably)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
BINGO! You've hit the nail on the head. Thanks. Here is the query that does that:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT (
SELECT TOP 1 OrderID
FROM Orders O2
WHERE CustomerID = O1.CustomerID
ORDER BY Inserted
)
FROM Orders O1
GROUP BY CustomerID
)
Of course this query relies on the existence of OrderID, a primary key of the Orders table. I didn't define it in my original question but just about every table should have a primary key anyway and just about all my tables usually do 
The great thing about this query is that the outermost query can have a SELECT * and, as you say, could even join the Orders table with any other table that might contain linked information on the orders.
Thanks again. It might seem insignificant but you've just helped me solve a problem I've been grappling with for years and never really bothered to definitively put to bed.
|
|
|
|
|
You are definitely welcome, once you get your head around SQL it can be very rewarding making the dammed thing do what you want. Then the bastards introduce a new way of doing something (CTEs come to mind) and you have to start all over again!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<edit>low on caffeine => dysfunctional brain</edit>
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
modified 27-May-13 17:15pm.
|
|
|
|
|
Jörgen Andersson wrote: I believe one subquery is unnecessary. Try:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT TOP 1 OrderID
FROM Orders O1
GROUP BY CustomerID
ORDER BY Inserted
)
I'm not sure I understand that inner query. For one OrderID in the SELECT clause should surely be part of an aggregate function seeing as it is not in the GROUP BY clause. Also, for Inserted to be contained in the ORDER BY clause it will also have to be part of an aggregate function. What am I missing?
|
|
|
|
|
You're not missing anything, it's me having had to little coffee and a non-functional brain.
Sorry 'bout that.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Hi
I had installed SSE2008 on a Small business 2008 server.
There were two of these servers on the domain.
The server I installed on was not the main server (if that makes sense)
In any case, they reinstalled the main server, but now I cannot start the SQLSERVER agnet service. The way I understand it, when you install an instance of SSE, there are certain windows permissions set up, and now since they re-installed Small Business server, thers permissions are no longer valid. I can go into the SSE server using Management Studio, but my appplication cannot see the server. Previously whet the server was rebooted, my application also could not see the server, but then by starting the service: SQLAgent$SQLEXPRESS it worked.
Any ideas how I can fix this? (I assume re-installing a new instance of SSE woulod work, since it would create new windows accounts? But this seems like the long way around and not a very elegant solution?)
|
|
|
|
|
We have 900,000 veterans with claims in the VA. It's clear the government either cannot or will not solve this, but it needs to be solved.
I wonder one thing: is there a way to crowdsource the solution?
There are some brilliant developers and data analysts on this board. While I don't count myself among these luminaries, I feel that we could solve this.
Cheers,
Daaron
|
|
|
|
|
Daaron wrote: is there a way to crowdsource the solution?
Filed under "Silver Bullet".
What's a VA?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: What's a VA?
Presumably the US Veterans Administration.
|
|
|
|
|
Daaron wrote: is there a way to crowdsource the solution?
You need to define the problem first.
Use the best guess
|
|
|
|
|
You are getting roasted because this is entirely the wrong place to discuss this issue.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Those who seek perfection will only find imperfection
nils illegitimus carborundum
me, me, me
me, in pictures
|
|
|
|
|
|
Daaron wrote: crowdsource the solution?
Solution to what exactly?
As a guess you are referring to back claims. If so then the back log is probably caused by a lack of humans an/or effective process control. Software might help with a solution to either of those, but software itself is not a solution.
|
|
|
|
|
Hi,
I am going to create a database in MySql to show the information on the website page.
I have data in my excel sheet it is related to Bollywood Movies / Songs.
Excel Sheet coloum details : Songs Title, Movie Name, Year, Singer, Music Director, Lyrics, Picturised, Actors, Banner,
Producer, Director, Music Company. I have Posters of Movies, pictures of all actors, Music directors, Singers etc.
Now my question is :
How can I design the database to show the record on the page.
1. If any user click on the particular Movie
the table should show the details are :
Song title | Singer | Music Director | Lyrics | Picturised
Poster of the movie will show on left / right side of the same page
2. If any user click on the particular singer
the table should show the singer's filmography details are :
Movie | Year | Music Director | Music Company
3. If any user click on the particular Movie the point no. 1 will be repeat
4. If any user click on the particular Music Director
the table should show the Music Director's filmography details are :
Movie | Year | Director | Music Company
5. If any user click on the particular Movie the point no. 1 will be repeat
it will be same for any actor, Music Director, Lyrics, Director, Banner etc..
=====
How many tables should I make and the table structure with primary / foreign key info for above said.
Thanks in advance
Muveen
|
|
|
|
|
Muveen_Delhi wrote: How many tables should I make
That depends on the design; I'd recommend to normalize the database-model, that way you'll have a guaranteed correct and consistent design. Alternatively, you can download a pre-built design on databaseanswers.org[^].
Muveen_Delhi wrote: How can I design the database to show the record on the page.
Databases do not show things; they store things. What kind of app would use the database? A webapplication, a desktop application, something on a phone?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I want to develop a website using PHP for that use I need this database.
|
|
|
|
|
Do a search for tutorials creating a music application (book would be similar), work through the tutorial.
You question shows a dramatic lack of understanding of what you are attempting, the tutorials will deepen that understanding and allow you to ask sensible questions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have the following tables.
Table SS
SID SNAME
== =====
1 AAA
2 BBB
3 CCC
Table PP
PID PNAME
== =====
1 Phase1
2 Phase2
3 Phase3
Third table (COMP) data may look like the following.
ID SID PID
== === ===
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
ID here in third table is autoincrement. I need to insert a new record in SS table and it can have mapping to many Phases in PP table.
So I want to write a
INSERT INTO COMP (col1,col2)
SELECT (col1,col2)
FROM table...
So ultimately new records needs to be inserted into the last table with a SID map and different PID.
How can I accomplish the same?
Thanks in advance.
CodingRocks
ASP.NET,C#.NET Programmer
BANGALORE
"Winners don't do different things. They do things differently. ...
|
|
|
|
|
Assuming you have a list of PIDs you want inserted you require 3 statemants,
<pre lang="SQL">Declare @NewID int
Insert SS (fields).Values
Set @NewID = scope_identity()
insert Comp(SID,PID)
Select @NewID, PID from listofpids</pre>
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
EDIT: I Guess this makes my problem clearer
i have 2 tables
Mother with Columns (Name,NameKey)
and Children with Columns(child,childkey,Mother.NameKey)
i want to connect 2 tables
and insert many children in 1 mother
is this code possible? can't try it right now
insert into children(mother.namekey,child)<br />
values<br />
(Select namekey from mother where name='TELMA'),BEN,RON,DONNA)<br />
i know my codes above have errors even w/o trying it.
i just want to know how to make that happen and get this output
MOTHER- CHILDREN
TELMA..............BEN
............................RON
...........................Donna
modified 24-May-13 22:59pm.
|
|
|
|
|
Have you tried (have assumed that this is SQL Server)
insert into family (children) values
select 'ben'
union
select 'ron'
union
select 'donna'
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
)