MIN/MAX issue

/*
I have posted DDL below for the issue i have. When you look at the output
from marc1, I would like to retrieve the minimum and maximum vehicle_id's
based on Vh_make_id
so as my output would look something like:
min max
-- --
3040501 3131887
3040443 3049312
How can I do this?
*/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[marc1]
GO
CREATE TABLE [dbo].[marc1] (
[Vehicle_id] [int] NULL ,
[Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_mfg_yr_id] [smallint] NULL ,
[Vh_mfg_yr_band_id] [tinyint] NULL ,
[Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_mfg_age_id] [tinyint] NULL ,
[Vh_group_id] [smallint] NULL ,
[Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_registration_id] [int] NULL ,
[Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_make_id] [smallint] NULL ,
[Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into marc1 values(3040501, '- QMVOG 02 VOL00101200539ST64268',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268, 05
,
05, '', '-', 'Not Defined', 312, 'VOLVO')
insert into marc1 values(3131887, '- QMVOG 03 VOL00101200539ST64268',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268, 05
,
05, '', '-', 'Not Defined', 312, 'VOLVO')
insert into marc1 values(3040443, '- QMVOG 01 MER00101200539ST766417',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
'05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
insert into marc1 values(3049312, '- QMVOG 02 MER00101200539ST766417',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
'05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
select * from marc1See if this helps
select min(Vehicle_id)as [max],max(Vehicle_id)as [min] from marc1
group by Vh_make_desc
order by [max] desc
BTW, your table has no primary key nor designed well in terms of
normalization.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
> /*
> I have posted DDL below for the issue i have. When you look at the output
> from marc1, I would like to retrieve the minimum and maximum vehicle_id's
> based on Vh_make_id
> so as my output would look something like:
> min max
> -- --
> 3040501 3131887
> 3040443 3049312
> How can I do this?
> */
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[marc1]
> GO
> CREATE TABLE [dbo].[marc1] (
> [Vehicle_id] [int] NULL ,
> [Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_mfg_yr_id] [smallint] NULL ,
> [Vh_mfg_yr_band_id] [tinyint] NULL ,
> [Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_mfg_age_id] [tinyint] NULL ,
> [Vh_group_id] [smallint] NULL ,
> [Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_registration_id] [int] NULL ,
> [Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_make_id] [smallint] NULL ,
> [Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into marc1 values(3040501, '- QMVOG 02
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3131887, '- QMVOG 03
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3040443, '- QMVOG 01
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> insert into marc1 values(3049312, '- QMVOG 02
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> select * from marc1
>|||sorry im having a sleepy day
select min(vehicle_id), max(vehicle_id), Vh_make_id from marc1 group by
Vh_make_id|||SELECT MIN(Vehicle_Id), MAX(Vehicle_Id)
FROM marc1
GROUP BY Vh_make_id
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
> /*
> I have posted DDL below for the issue i have. When you look at the output
> from marc1, I would like to retrieve the minimum and maximum vehicle_id's
> based on Vh_make_id
> so as my output would look something like:
> min max
> -- --
> 3040501 3131887
> 3040443 3049312
> How can I do this?
> */
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[marc1]
> GO
> CREATE TABLE [dbo].[marc1] (
> [Vehicle_id] [int] NULL ,
> [Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_mfg_yr_id] [smallint] NULL ,
> [Vh_mfg_yr_band_id] [tinyint] NULL ,
> [Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_mfg_age_id] [tinyint] NULL ,
> [Vh_group_id] [smallint] NULL ,
> [Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_registration_id] [int] NULL ,
> [Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_make_id] [smallint] NULL ,
> [Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into marc1 values(3040501, '- QMVOG 02
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3131887, '- QMVOG 03
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3040443, '- QMVOG 01
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> insert into marc1 values(3049312, '- QMVOG 02
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> select * from marc1
>|||You mean
select min(Vehicle_id)as [min],max(Vehicle_id)as [max] from marc1
group by Vh_make_desc
order by [max] desc
--
Regards
R.D
--Knowledge gets doubled when shared
"Uri Dimant" wrote:

> See if this helps
> select min(Vehicle_id)as [max],max(Vehicle_id)as [min] from marc1
> group by Vh_make_desc
> order by [max] desc
> BTW, your table has no primary key nor designed well in terms of
> normalization.
>
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
>
>|||Hi Marc,
try this:
Select Vh_make_id, MIN(Vehicle_id) as minvalue, MAX(Vehicle_id) as
maxvalue
>From marc1
GROUP BY Vh_make_id
HTH, jens Suessmeyer.|||Yep, sorry
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:42FF4DAE-810D-47B7-908B-CE2EC6D70B85@.microsoft.com...
> You mean
> select min(Vehicle_id)as [min],max(Vehicle_id)as [max] from marc1
> group by Vh_make_desc
> order by [max] desc
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Uri Dimant" wrote:
>