How to get the most new value registered...

I have this information,
header table
documento fecha_entrada
100000 2005-11-25
100001 2005-11-25
100002 2005-11-26
detail table
documento linea numero_producto fecha
100000 1 X 2005-11-25 10:00
100000 2 Y 2005-11-25 10:01
100001 1 M 2005-11-25 11:02
100001 2 S 2005-11-25 11:03
100001 3 R 2005-11-25 11:04
100002 1 R 2005-11-25 11:04
I want to make a SELECT query to obtein this
docuemnto linea numero_pro fecha
fecha_entrada
100000 2 Y 2005-11-25 10:01
2005-11-25
100001 3 R 2005-11-25 11:04
2005-11-25
100002 1 R 2005-11-25 11:04
2005-11-26
I only want the header and ONE row of the detail... the row has to be the
most recent captured..On Fri, 25 Nov 2005 16:56:18 -0600, Myriam Cerda wrote:

>I have this information,
(snip)
>I only want the header and ONE row of the detail... the row has to be the
>most recent captured..
Hi Myriam,
Try this:
SELECT h.documento, d.linea, d.numero_producto,
d.fecha, h.fecha_entrada
FROM header AS h
INNER JOIN detail AS d
ON d.documento = h.documento
WHERE d.fecha = (SELECT MAX(d2.fecha)
FROM detail AS d2
WHERE d2.documento = h.documento)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo, Thanks for your prompt response, It's works...
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sf6fo1dhmjj05h6vqbrhs3htb80uffvbd3@.
4ax.com...
> On Fri, 25 Nov 2005 16:56:18 -0600, Myriam Cerda wrote:
>
> (snip)
> Hi Myriam,
> Try this:
> SELECT h.documento, d.linea, d.numero_producto,
> d.fecha, h.fecha_entrada
> FROM header AS h
> INNER JOIN detail AS d
> ON d.documento = h.documento
> WHERE d.fecha = (SELECT MAX(d2.fecha)
> FROM detail AS d2
> WHERE d2.documento = h.documento)
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to get table records position in comparison to other records based on numeric field?

Hi,

Let's say I have 1000 registered users in database table and each of them has numeric ranking value.

How can I get the position of each user in comparison to other users ranking value?

If you're using SQL Server 2005, you can use the row_number() function. Are you?

Don

|||Hi,|||

Hi,

Yes, I am. Can you post some example code?

|||maybe you can post your table DDL, some sample data and the result that you want ?|||

Hi,

Solution found! Here's the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>"

SelectCommand="SELECT [usernickname], ROW_NUMBER() OVER(ORDER BY userrankavg DESC) AS 'position' FROM [dbusers]"
</asp:SqlDataSource>

|||

Hi,

That doesn't actually solve my problem.

The code does display position for each record when all records are selected from table, but doesn't when WHERE attribute is specified.

How can I get position for a specific record that I specify with WHERE attribute?