Question about MAX()

I have a table which has case numbers and version numbers. The same case number has different version numbers. I was wondering if there was a way to select all the case numbers with the highest version numbers. Pretty much what I am looking for is as follows:

TABLECASES:

CaseNumber VersionNum

1234 1

1234 2

1234 3

3567 1

3567 2

Upon running my query..I want something as follows:

CaseNumber VersionNum

1234 3

3567 2

Can sombody point me how to construct the query..thanks

SELECT CASENUMBER, MAX(VERSIONNUM)
FROM TABLECASES
GROUP BY CASENUMBER

It should be that simple,

Roberto Hernández-Pou
http://community.rhpconsulting.net

|||

Thanks a lot for this prompt response. Now suppose I have additional information in this table that differs from version to version..something as follows:

TABLECASES:

CaseNumber VersionNum VersionInfo

1234 1 finapp

1234 2 reopened

1234 3 closed

3567 1 finapp

3567 2 reopened

Now if I use the query that you suggested which is

SELECT CASENUMBER, MAX(VERSIONNUM)
FROM TABLECASES
GROUP BY CASENUMBER

I get the following:

CaseNumber VersionNum

1234 3

3567 2

Now..what I want instead is something like:

CaseNumber VersionInfo

1234 closed

3567 reopened

The version info is contingent upon the version number..is there a way to modify the sql to do that...thanks

|||

SELECT a.CASENUMBER, a.VersionInfo FROM TABLECASES a INNER JOIN (SELECT CASENUMBER, MAX(VERSIONNUM) as maxVersionNum FROM TABLECASES GROUP BY CASENUMBER) b ON a.CaseNumber =b.CaseNumber AND VERSIONNUM=b.maxVERSIONNUM

|||If you use SQL Server 2005, you can also do this:

SELECT a.CASENUMBER, a.VersionInfo FROM

(SELECT CASENUMBER, VersionInfo , ROW_Number() OVER(partition by CASENUMBER ORDER BY VERSIONNUM DESC) as rankNum

FROM TABLECASES) AS a

WHERE a.rankNum=1

question about display mask & select number N to M records

Two questions:
1. With SQL, how can I setup display mask for value:
eg. display 100000 as $100,000
2. How can I select number N to M records in a table.
eg. select No. 50 - 100 records from a table. ( not top 50)
Thanks,
Guyang> 1. With SQL, how can I setup display mask for value:
> eg. display 100000 as $100,000
(a) I wouldn't rely on Enterprise Manager for data viewing / modification...
use a development tool for that, like Query Analyzer.
(b) there is no such thing as a "display mask" in SQL Server... this is
something that cute GUIs do. The data is not stored that way; if you want
it to be *presented* that way, write a view, e.g. SELECT CONVERT(VARCHAR,
moneyColumn, 1) FROM table
> 2. How can I select number N to M records in a table.
> eg. select No. 50 - 100 records from a table. ( not top 50)
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC
If you really need it to come back 50 -> 100, then
SELECT * FROM
(
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC) y
ORDER BY some_column

Question about data type sqlserver ?

I have two problem :

+ The first, This is table store all items in bookshop system

tblItems:
IDItem Identity(Auto number)
Namebook nvarchar2
Price nvarchar2
Chapters nvarchar2
Weight nvarchar2 (weight of book)
....
I design data type for Chapter,or Price,Weight is nvarchar2 ? <--I wrong ? (I want to refer to principle of design the database)

+ The second ,When i design Price is the int datatype ! The default value is 0 ( I don't want to have this value ,i want to it is a empty field )

I really sorry because i ask too much ! Because i am a new programming !

Any Help or Advice would like appreciately ! Thanks u !

In my opinion you should use these datatypes:
IDItem Identity(Auto number)
Namebook nvarchar2
Price decimal
Chapters integer
Weight decimal orinteger depending upon the unit of measure
I don't understand what you are saying about Price and the integerdatatype and having a default value of 0. Unless you do somethingspecial SQL Server will insert a NULL into that field if you haven'tspecified a value.

|||

Thank tomorton very much !
I really don't understand what happen to me !

But I design again table ,everything is good !

Question about Converting Crystal syntax into SQL or VB

I'm having an issue with a date field in Access. I have imported data
from a database and the date field is a HUGE number rather than an
actual date. I have a formula from crystal reports to convert the
number into a date, but I;m not sure how to convert the crystal report
formula into a SQL or VB formula. the formula is below.
NumberVar Yyyy ;
NumberVar Mm;
NumberVar Dd;
Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
Date (Yyyy, Mm, Dd)
I would appreciate any help!
Thanks!
JROn 3 Feb 2006 12:23:12 -0800, jennifer.rodgers@.fidessa.com wrote:

>I'm having an issue with a date field in Access. I have imported data
>from a database and the date field is a HUGE number rather than an
>actual date. I have a formula from crystal reports to convert the
>number into a date, but I;m not sure how to convert the crystal report
>formula into a SQL or VB formula. the formula is below.
>
>NumberVar Yyyy ;
>NumberVar Mm;
>NumberVar Dd;
>Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
>Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
>Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
>Date (Yyyy, Mm, Dd)
>I would appreciate any help!
>Thanks!
>JR
Hi JR,
Try if this works for you:
SELECT DATEADD(year,
(CALLDAT_HDW / 65536) - 2000,
DATEADD(month,
(CALLDAT_HDW % 65536) / 256,
DATEADD(day,
CALLDAT_HDW % 256,
'19991231')))
Hugo Kornelis, SQL Server MVP

Question - I think it has got to do with grouping (although not su

Hello,
I have created a list in visual studio reporting services with all
outstanding invoices for a range of customers (where customer number between
'From' and 'To' parameter). I have also created a statement in the reporting
layout, but now I would like to know I can make sure that the statement for
every customer appears on a new page?
Statement looks as follows:
---
Customer number: XXXXXXXXXX
Customer name: John Doe
Adress: Test street 23
...
======= Invoice details (this is a table which contains all outstanding invoices for
this customer including the open amounts).
Please help.
Thanks!
CheersOn Jan 10, 3:19 am, DHL <D...@.discussions.microsoft.com> wrote:
> Hello,
> I have created a list in visual studio reporting services with all
> outstanding invoices for a range of customers (where customer number between
> 'From' and 'To' parameter). I have also created a statement in the reporting
> layout, but now I would like to know I can make sure that the statement for
> every customer appears on a new page?
> Statement looks as follows:
> ---
> Customer number: XXXXXXXXXX
> Customer name: John Doe
> Adress: Test street 23
> ...
> =======> Invoice details (this is a table which contains all outstanding invoices for
> this customer including the open amounts).
> Please help.
> Thanks!
> Cheers
You would set your Group properties to 'Page break at end.' You would
do this via: right-clicking the table/matrix control >> select
Properties >> select Groups tab >> select the Edit... button >> then
select 'Page break at end.' Hope this helps.
Regards,
Enrique Martinez
Sr Software Consultant

Question

Can anyone tell me the limitation of the
xp_getfiledetails. I am executing this xp on a database
backup file and it's giving me a negative number?? The
real size is about 7 G......
Help pleasE!
Thanxs
wrote:
> Can anyone tell me the limitation of the
> xp_getfiledetails. I am executing this xp on a database
> backup file and it's giving me a negative number?? The
> real size is about 7 G......
> Help pleasE!
> Thanxs
xp_getfiledetails is an undocumented extended stored procedure and
therefore not supported by Microsoft. Looking at the output, it appears
the "Size" column is an INT, limited to just over a value of 2 billion.
When I run the procedure against a 3GB test file, I get: -1073741824.
Which appears to translate to the max INT size + the ABS() of the
returned value plus 1.
select cast(2147483647 as bigint) + cast(1073741824 as bigint) + 1
In any case, if the file were greater than 2x the largest integer size,
I'm not sure you could easily calculate the real value.
You may have to find an alternative.
David G.