How to select biggest number less than 9000?

Question

How to select biggest number less than 9000?

Edvard Korsbæk

SSCertifiable

Points: 5999
More actions
October 4, 2017 at 1:09 am

#328943

Hi all!
I have an field 'MEDLEMSNUMMER', where i have found the highest number the very easy way:
Select max(MEDLEMSNUMMER) from dbo.medlemme
Its so, that numbers less than 9000 are 'Normal' members, and i have used app. 400 of them.
.Numbers over 9000 have free membership.
so how do i find MAX(MEDLEMSNUMMER), where MEDLEMSNUMMER less than 9000?
For formal reasons, the table involved is:
CREATE TABLE [dbo].[medlemme](
    [id] [INT] IDENTITY(1,1) NOT NULL,
    [MEDLEMSNUMMER] [SMALLINT] NULL,
    [FORNAVN] [CHAR](30) NULL,
    [EFTERNAVN] [CHAR](30) NULL,
    [GADE] [CHAR](30) NULL,
    [ADRESSE_2] [CHAR](30) NULL,
    [POSTNUMMER] [CHAR](10) NULL,
    [BYNAVN] [CHAR](30) NULL,
    [TLF_NR] [CHAR](20) NULL,
    [FAX_NR] [CHAR](20) NULL,
    [E_MAIL] [CHAR](30) NULL,
    [LAND] [CHAR](3) NULL,
    [INDMELDT] [INT] NULL,
    [KONTINGENT] [INT] NULL,
    [UDMELDT] [INT] NULL,
    [AKTIV] [TINYINT] NULL,
    [MEDLEMSMAT] [CHAR](3) NULL,
    [NOTER] [TEXT] NULL,
CONSTRAINT [are_pk_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Best regards
Edvard Korsbæk

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Select max(MEDLEMSNUMMER) from dbo.medlemme where MEDLEMSNUMMER < 9000

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Edvard Korsbæk SSCertifiable Points: 5999 More actions · Answer 2

Thanks!

Sometimes things are much more simple than iI belived.

Best regards

Edvard