Showing posts with label latest. Show all posts
Showing posts with label latest. Show all posts

Wednesday, March 21, 2012

Query to return latest record, multiple join fields

Hi
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql

Tuesday, March 20, 2012

Query to Retrieve Latest Row from each group !!

Hi SQL Query Expert,
My table looks like this:
CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
1 <NULL> ABC Company 4/7/2005
11:10:10 a.m.
2 1 XYZ Company
4/8/2005 10:10:12 a.m.
3 1 AAA Company
4/8/2005 12:10:00 p.m.
4 <NULL> BBB Company 4/8/2005
1:00:00 p.m.
5 4 CCC Company
4/8/2005 2:00:00 p.m.
6 <NULL> DDD Company 4/8/2005
3:00:00 p.m.
Basically record 2 and 3 are childs of record 1. Record 5 is child of record
4. Record 6 is a parent.
Could you please give me an example on how to retrieve rows with CONTRACT_PK
equals to 3, 5 and 6 from the above table?
My goal is to retrieve the latest child row if the parent has children. If
the parent doesn't have child(s), then it retrieves parent row. Here 3 and
5
are all latest child of parent 1 and 4. Parent 6 doesn't have child, so, i
t
should get retrieved too.
The table could have 1000 rows and they all fall in the same pattern for the
records retrieval.
Thank you so much!!!
-adamTry This
Select IsNull(C.CONTRACT_PK, P.CONTRACT_PK) ContractPK,
IsNull(C.PARENT_PK, P.PARENT_PK) ParentPK,
IsNull(C.CONTRACTOR_NAME, P.CONTRACTOR_NAME) Contractor,
IsNull(C.CREATED_DATE, P.CREATED_DATE) CreatedDate
From Table P
Left Join Table C
On C.Parent_PK = P.Contract_PK
And C.Created_Date = (Select Max(Created_Date)
From Table
Where Parent_PK =
C.Parent_PK)
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>|||adam,
Do not post the same problem twice, it does not help. Check your first threa
d.
AMB
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>

Saturday, February 25, 2012

Query the latest info

Hi All,

I have thsi list of record

Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004

sp i want to list out cars that status solid has been sold

so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks

Regards,
ShaffiqYou want to list only the cars that have been sold?

Select * from table where status = 'sold'|||You want to list only the cars that have been sold?

Select * from table where status = 'sold'

hi,
its easy, but sometime cars that has been sold were return back , so this not considered sold. I want to display cars that solid has been sold...sometimes got status sold, return and sold back... but sometimes only sold..for this case only Pl3 and PL4.. any idea? thanks|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')

Thx,
I' d tried before but it will not display cars that have tree status( sold, return, sold) for this case you can see at PL4|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'

Hi r937,

Thx for the solution it works well thank also to pshisbey

Query Taking too Long

Below is my query which is taking a long time to execute, DB is SQL Server 2005 through a web Application
I have downloaded the latest MS SQL 2005 driver 1.xxx and still the query takes long to execute

The Description field is a Full_text indexed catalog column
the p.vendornumber is a primary key same with c.ID

Any one have an idea why it is taking this long to run

The Execution Time is: 13640 ms Which I think is very long

SELECT Upper(p.Type) Type,p.Modelname,p.partno,Upper(p.description) description,
Upper(p.classification)classification,p.vendornumber,p.mfg,
p.price,c.CompanyName,c.City,c.State,p.thumbnail
FROM P_all p, Acts c
WHERE p.vendornumber = c.ID
AND CONTAINS(p.Description, '"helmet*"')
Order by p.VendorNumber

Thanks

First off, since you are using a WHERE criteria of [ p.VendorNumber ], the ORDER BY is pointless, and depending upon the resultset, perhaps even wasting time.

Then, I think the most significant part of the query is

FROM P_all p, Acts c

which 'may' have the effect of causing a CROSS JOIN.

Use the 'proper' form for a JOIN, including the JOIN conditions. (I've assumed that VendorNumber is common to both tables, if that is incorrect, then use the correct linking column.)

Code Snippet


SELECT
Type = upper( p.Type ),
p.ModelName,
p.PartNo,
Description = upper( p.Description ),
Classification = upper( p.Classification ),
p.VendorNumber,
p.Mfg,
p.Price,
c.CompanyName,
c.City,
c.State,
p.Thumbnail
FROM P_All p
JOIN Acts c
ON p.VendorNumber = c.ID
WHERE contains( p.Description, '"helmet*"' )
ORDER BY p.VendorNumber


|||Thanks Arnie,

The changes still did not solve the problem.
|||

what are indexes on these table. And also remove Upper functions from all column and see the performance. and post back the result of the query without upper function

Madhu

|||There are indexes on the table. The culprit column "Description, is a Full-text indexed catalog column. The main searches are on this column based on character search. I was using the "like" search before creating the "Full-text indexed catalog.

The Upper function is not the problem, the query takes longer to run without the Upper function.

Are there more things I need to do in the SQL 2005 Database? I have installed the latest MS JDBC drivers for MS SQL 2005.

Thanks
|||If by chance, this database was migrated to SQL 2005 from SQL 2000, you would need to rebuild the indexes, and update statistics.