|
i am having some trouble with a correlated-subselect-statement (using mysql 5.0):
i have a table that looks simplyfied like this:
myTable:
--------------------------
ColID NameID ColDate
--------------------------
1 1 2008-10-01
2 1 2008-07-01
3 2 2008-06-11
4 3 2008-09-12
5 4 2008-03-15
6 2 2008-11-21
7 3 2008-08-19
8 4 2008-01-30
--------------------------
now i need for every nameID to select only the row with the latest date.
desired result:
--------------------------
ColID NameID ColDate
--------------------------
1 1 2008-10-01
4 3 2008-09-12
5 4 2008-03-15
6 2 2008-11-21
--------------------------
i tried the following select-statement:
SELECT * FROM myTable AS T1 WHERE ColDate=(SELECT MAX(T2.ColDate) FROM myTable AS T2 WHERE T1.NameID=T2.NameID)
but got the error message: #1064 - You have an error in your SQL syntax.
thanks for any help with this.
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
First I have to say that I haven't used MySQL so I'm guessing here.
Typically table aliases do not use AS keyword so perhaps the statement should be:
SELECT * FROM myTable T1 WHERE ColDate=(SELECT MAX(T2.ColDate) FROM myTable T2 WHERE T1.NameID=T2.NameID)
Also there may be problems if there are two records with the same date since then your subquery will return two rows and that's not acceptable when using = operator. If this happens, use for example both MAX(ColDate) and MIN(ColID) (or something similar) to get only one row from subwuery.
edit: Another option could be using GROUP BY ... HAVING ... clauses
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
modified on Tuesday, November 4, 2008 12:19 PM
|
|
|
|
|
Tried it with and without AS-keyword, but without any success. Still the same error message. I realized earlier, that the existence of two entries with the same date might cause trouble in this query, but that isn't the case here. I changed all entries in the table to show different days but I still get the error message.
So next thing tomorrow when I am not that sleepy any more: I will give GROUP BY...HAVING a try.
Thanks for your answer so far, I will come back, when I have a (hopefully) positive result.
|
|
|
|
|
I checked the syntax for MySQL 5 and everything in your example seems right.
However, if I understood you correctly, that's not the actual statement you use, so you may have a 'hidden' typo in the actual statement (for example missing parenthesis or so on). If still having problems with that version tomorrow, perhaps you can post the full statement.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I have try this in MySQL
<br />
select distinct nameid,<br />
(select t1.colid from mytable as t1 where t1.nameid=mytable.nameid limit 1) colid,<br />
(select t1.coldate from mytable as t1 where t1.nameid=mytable.nameid order by coldate desc limit 1) coldate<br />
from mytable
but result is this:
<br />
1 1 2008-10-01<br />
2 3 2008-11-21<br />
3 4 2008-09-12<br />
4 5 2008-03-15
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
That might be, what I am looking for (I am not sure, since I didn't describe the problem entirely in my first question - see my answer to SomeGuyThatsMe below). I tried that query, but still got an error message.
|
|
|
|
|
try
SELECT NameId, Max(Date)
FROM table
WHERE whatever your clause is
GROUP BY NameId
i think that will work, but i'm not sure if there are any differences between MySql and SqlServer for doing this kinda stuff, i havent used MySql
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
No, sorry, that didn't work. As I said, the table in my example is simplified: There are a few more columns, the important one called "LocationID". For every NameID I need the LocationID of the latest date, to join the result with further tables (NameID and LocationID being foreign keys from other tables, as you might have already guessed). So if I use the query proposed by you, I get a table with a row for every nameID, showing the latest date (as desired), but not the LocationID relevant to that date but the first LocationID of each NameID's group.
Uhm, I hope that was comprehensible?
But thanks for you answer.
|
|
|
|
|
i think it makes sense, you want the nameID, most recent date, and the locationID for the most recent date for a given name correct? If not let me know what you need to return and where those fields are and i can try to come up with something to help.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Yes, that's right. I need to find the most actual locations for a given list of names. I should have described this earlier.
Would be great, if you'd knew an answer. 
|
|
|
|
|
SELECT t1.NameId, Max(t1.DateTime), t2.Location
FROM Table1 t1
INNER JOIN Table2 t2
on t1.LocationId = t2.LocationId
where something
Group By t1.NameId, t2.Location
should work for you as long as your first group by is the nameId. it worked like that for my test i even selected a half dozen or so columns from another table.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
That one still didn't work. The problem is, when using MAX(ColDate) I get the latest date in that group but not the LocationID from the latest date's row, but the LocationID from each group's first row.
But don't bother, I found another solution to my problem (see below).
But thanks alot for your help.![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
Okay, since none of the solutions proposed above worked for me, I came up with something different.
This one's working fine:
SELECT s1.NameID, s1.LocationID, s1.ColDate
FROM myTable s1
LEFT JOIN myTable s2 ON s1.NameID= s2.NameID AND s1.ColDate < s2.ColDate
WHERE s2.NameID IS NULL
Returns for every NameID only the LocationID with the most actual date, exactly as desired.
Thanks to Mika Wendelius, Blue_Boy and SomeGuyThatIsMe for their answers and the time they spent trying to solve my problem.
|
|
|
|
|
I have found that JOINs are much better than subqueries on a number of levels.
|
|
|
|
|
I have a DataSet which contains three rows with 4 columns in each row. I want to print out the contents of the DataSet so I have written this:
foreach (DataRow row in MyDataSet.Tables[0].Rows)
for (int i = 0; i < MyDataSet.Tables[0].Columns.Count; i++)
Console.WriteLine("Value {0}", row.ItemArray[i]);
Is there a better way than having a for statement and looking at the ItemArray?
I tried the following but I only get the column names:
foreach (DataRow row in MyDataSet.Tables[0].Rows)
foreach (DataColumn column in dsUpdates.Tables[0].Columns)
Console.Write(column.ToString());
If I am missing the basics can someone tell me where or what to read to understand handling DataSets? 
|
|
|
|
|
Instead of
Console.Write(column.ToString());
try using the indexer for row like:
Console.Write(row[column].ToString());
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Dear All,
Iam new to MS SQL Server 2000 Administration. I have a small problem.
I want to Configure Auto Database Replication. I have 2 databases
1) Database 1
2) Database 2
The data should be copied from Database 1 to Database 2 when ever a insert, delete, update happened in the Database 1.Any changes in the Database 2 Should not affect to Database 1.
Overall if any thing chages in the Database 1 ,Same changes should
happen in Database 2.
Please help me on this.
Thanks & Regards
Veeresh
i want to join this group
|
|
|
|
|
|
In Visual Studio, from the server explorer I am right clicking on stored procedure and click Step into Stored Procedure on the menu pad. It comes back to allow me to enter in the parameters but I cannot step through the program starting at the red dot. The dot says: "Break point will not currently be hit. Unable to bind sql break points at this time. Object contain break point not loaded." How can I clear this up so I can debug the procedure?
|
|
|
|
|
But I use the following rule: If I can't debug a stored procedure using pen and paper it is too complicated. This rule has served me well.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
I faced some time ago some known problems with server explorer. So try following:
- delete all connections permanently from server explorer
- restart visual studio (better yet the whole machine)
- add new connection in server explorer to your database and try debugging again
This may solve the problem. At least it helped in my case.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi
I have been experimenting with ways of extracting relational data in forms of xml from SQL Server 2005. I have created some views containg and ID integer field and a XML field. The ID field is what i filter on when selecting data, and the XML field contains the actual data.
I came across a situation where the XML data was recursive in its nature. I have a product Hierarchy where products consists of a list of SubProducts. I solved this by creating a SQL Function which is called recursively in my view, retreiving all products that have the current product as parent. Something Like this:
SELECT
ProductID,
ProductName,
ParentID,
dbo.GetSubProducts(ProductID)
FROM
view_Product
This worked fine with small amount of data, but ended up being very slow when there was a medium amount of data.
I tried refactoring my view to use CTE, cause it seems the way to go.
The problem is i get a folder like structure, and not a XML like structure.
Like this:
Product1
Product2
Product1/Product2
Product1/Product2/Product3
Product1/Product2/Product4
Product3
Product4
What I wanted was like this:
Product1/Product2/Product3+Product4
Product2/Product3+Product4
Product3
Product4
I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.
I have not posted any sql as i have no real solution to the issue.
I hope you understand the problm anyhow.
Michael
|
|
|
|
|
Perhaps if you provide the CTE SQL you used to generate the incorrect result, it would be easier to see the problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I created a full example including tables, views and data required to test the SQL.
-------------------------------------------------------------------------------------------------
---- DATA STRUCTURE PREPERATION -------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Create a Test Schema
CREATE SCHEMA TEST
GO
-- Create a table with parent/child relationship.
CREATE TABLE [TEST].[Product](
[ID] [int] NOT NULL,
[Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentID] [int] NOT NULL,
[ParentName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_TEST_Product_1] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[ParentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Populate table with sample data.
INSERT INTO [TEST].[Product] VALUES (26,'TV',0, NULL)
INSERT INTO [TEST].[Product] VALUES (43,'Buy Settopbox',47,'Settopbox Group')
INSERT INTO [TEST].[Product] VALUES (47,'Settopbox Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (48,'Rent Settopbox',47,'Settopbox Group')
INSERT INTO [TEST].[Product] VALUES (82,'DR1',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (83,'DR2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (84,'Sverige TV1',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (85,'Sverige TV2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (88,'TV2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (97,'Basic TV',100,'Standard TV Group')
INSERT INTO [TEST].[Product] VALUES (98,'Plus TV',100,'Standard TV Group')
INSERT INTO [TEST].[Product] VALUES (100,'Standard TV Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (124,'Special TV Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (152,'Oprettelse TV',26,'TV')
INSERT INTO [TEST].[Product] VALUES (698,'SmileSport pakke',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (699,'HD Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (701,'Discovery Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (702,'Kids Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (82,'DR1', 97, 'Basic TV')
INSERT INTO [TEST].[Product] VALUES (83,'DR2', 97, 'Basic TV')
-- Create view that selects from the table and defines an xml column.
CREATE VIEW [TEST].[view_Xml_Product]
AS
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName,
(
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName
FOR XML PATH('Product'), TYPE
)
AS [xml]
FROM [TEST].[Product] P WITH (NOLOCK)
GO
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
---- THE RECUSIVE CTE THAT CREATES RECURSIVE XML DOCUMENTS ------------------------------
-------------------------------------------------------------------------------------------------
WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml])
AS
(
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName,
CAST(P.ID AS Varchar(200)) AS [Path],
P.[xml] AS [xml]
FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)
UNION ALL
SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
CAST(RTRIM(P2.ID) + '->' + P3.[Path] AS varchar(200)) AS [Path],
(
SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
P3.[xml] AS SubProductList
FOR XML PATH('Product'), TYPE
) AS SubProductList
FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)
INNER JOIN ProductTree AS P3 ON P2.ID = P3.ParentID
)
SELECT * FROM ProductTree ORDER BY 1
OPTION (MAXRECURSION 50)
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
When executing the CTE you recieve a "folder-like" structure as shown in the Path field. Actually the XML column has the same structure as the path column.
[b]Like this:[/b]
Product1
Product2
Product1->Product2
Product1->Product2->Product3
Product1->Product2->Product4
Product3
Product4
and the xml:
<Product>
<ID>26</ID>
<Name>TV</Name>
<ParentID>0</ParentID>
<SubProductList>
<Product>
<ID>124</ID>
<Name>Special TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>701</ID>
<Name>Discovery Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
</SubProductList>
</Product>
</SubProductList>
</Product>
[b]What I wanted was like this:[/b]
Product1/Product2/Product3+Product4
Product2/Product3+Product4
Product3
Product4
and the xml (not complete, but show the idea):
<Product>
<ID>26</ID>
<Name>TV</Name>
<ParentID>0</ParentID>
<SubProductList>
<Product>
<ID>124</ID>
<Name>Special TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>698</ID>
<Name>SmileSport pakke</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>699</ID>
<Name>HD Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>702</ID>
<Name>Kids Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>701</ID>
<Name>Discovery Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
</SubProductList>
</Product>
<Product>
<ID>100</ID>
<Name>Standard TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>97</ID>
<Name>Basic TV</Name>
<ParentID>100</ParentID>
<ParentName>Standard TV Group</ParentName>
</Product>
<Product>
<ID>98</ID>
<Name>Plus TV</Name>
<ParentID>100</ParentID>
<ParentName>Standard TV Group</ParentName>
</Product>
</SubProductList>
</Product>
</SubProductList>
</Product>
I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.
I hope you understand what I intend to do.
Michael Vivet
|
|
|
|
|
Hi,
First of all, you did a great job in creating this example! You've really put effort into it.
I've investigated this a bit and so far I haven't used the xml portion in any way. I think it's in this stage easier to play only with the path.
What I've done:
- created a test view based on the recursive query (it's easier to handle it in the next stage)
- in the CTE I locked the root to those records having parentid 0. In the previous version all the rows acted as parents
- changed the path to build the string in reverse order to gain correct path
- changed the join in second part of the cte (it was in wrong order)
So now the path is showing better results and also the parentid is giving the previous parentid, not the original parentid.
The view looks like this:
CREATE VIEW vw_test AS
WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml]) AS
(
SELECT P.ID,
P.[Name],
P.ParentID,
P.ParentName,
CAST(P.ID AS Varchar(200)) AS [Path],
P.[xml] AS [xml]
FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)
WHERE p.parentid=0
UNION ALL
SELECT P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
CAST(RTRIM(P3.[Path]) + '->' + cast(P2.ID as varchar(200)) AS varchar(200)) AS [Path],
(SELECT P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
P3.[xml] AS SubProductList
FOR XML PATH('Product'), TYPE) AS SubProductList
FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)
INNER JOIN ProductTree AS P3 ON P3.ID = P2.ParentID
)
SELECT * FROM ProductTree
The next phase was to remove the rows that have 'children'. I did it in a quick and dirty way but here's the query:
SELECT *
FROM vw_test a
WHERE NOT EXISTS( SELECT 1
FROM vw_test b
WHERE b.[Path] LIKE a.[Path] + '%'
AND b.[Path] != a.[Path])
ORDER BY 5
The next step would be to eliminate the rows that are sub level row and have the same parentid. In this process those rows should be combined so that the id part should be added to the previous row. For example rows:
ID Name ParentID ParentName Path
698 SmileSport pakke 124 Special TV Group 26->124->698
699 HD Package 124 Special TV Group 26->124->699
701 Discovery Package 124 Special TV Group 26->124->701
702 Kids Package 124 Special TV Group 26->124->702
should be converted to single row having path:
26->124->698 + 699 + 701 + 702
One way to do this is to create a stored proc which uses this select and then using cursor combine rows.
One thing though. I'm quite pessimistic with the efficiency. So basically your original idea of using a function may be better when bottlenecks in that function are eliminated.
Also few observations.
- You shouldn't use 0 for the root parent id. Use NULL instead.
- Consider creating a foreign key between parentid and id.
- Don't save the parentname in the table. Fetch it when needed so you won't have possible mismatches in data and you don't have to update several places when name is changed.
Does this help you forward?
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
