min with a bit

[Not Me]

Hi,

I'm trying to grab records with a priority over those marked as yes (-1) in
a certain field.

Trying "select id, min(bit) from tab group by id" does not work, as the min
operator doesn't work on bits.

Is there an alternative to my query?

Many thanks,
Chris

[mountain man]

Re: min with a bit

min(cast(delete d as int))



"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
news:buoari$thc $1@ucsnew1.ncl. ac.uk...[color=blue]
> Hi,
>
> I'm trying to grab records with a priority over those marked as yes (-1)[/color]
in[color=blue]
> a certain field.
>
> Trying "select id, min(bit) from tab group by id" does not work, as the[/color]
min[color=blue]
> operator doesn't work on bits.
>
> Is there an alternative to my query?
>
> Many thanks,
> Chris
>
>[/color]

[Not Me]

Re: min with a bit

"mountain man" <hobbit@souther n_seaweed.com.o p> wrote in message
news:opOPb.2351 5$Wa.14455@news-server.bigpond. net.au...[color=blue]
> "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> news:buoari$thc $1@ucsnew1.ncl. ac.uk...[/color]
[color=blue][color=green]
> > Trying "select id, min(bit) from tab group by id" does not work, as the[/color]
> min[color=green]
> > operator doesn't work on bits.
> >
> > Is there an alternative to my query?[/color][/color]
[color=blue]
> min(cast(delete d as int))[/color]

Thanks for that, I do though get an error when trying it, I guess it's
because I'm using an mdb file and linked tables to the sql server... any
other ideas? Could create a quick function I guess...

Cheers,
Chris

[Simon Hayes]

Re: min with a bit

"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message news:<buocfi$ub 9$1@ucsnew1.ncl .ac.uk>...[color=blue]
> "mountain man" <hobbit@souther n_seaweed.com.o p> wrote in message
> news:opOPb.2351 5$Wa.14455@news-server.bigpond. net.au...[color=green]
> > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > news:buoari$thc $1@ucsnew1.ncl. ac.uk...[/color]
>[color=green][color=darkred]
> > > Trying "select id, min(bit) from tab group by id" does not work, as the[/color][/color]
> min[color=green][color=darkred]
> > > operator doesn't work on bits.
> > >
> > > Is there an alternative to my query?[/color][/color]
>[color=green]
> > min(cast(delete d as int))[/color]
>
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...
>
> Cheers,
> Chris[/color]


Your question isn't really clear - a bit column can only hold 0,1 or
NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
an MSSQL query, then please consider posting the CREATE TABLE
statement for your table, as well as the exact query that you're
using, and the output you expect (sample data would also be useful).

Simon

[Igor Raytsin]

Re: min with a bit

How a bit could be (-1) ?


"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
news:buoari$thc $1@ucsnew1.ncl. ac.uk...[color=blue]
> Hi,
>
> I'm trying to grab records with a priority over those marked as yes (-1)[/color]
in[color=blue]
> a certain field.
>
> Trying "select id, min(bit) from tab group by id" does not work, as the[/color]
min[color=blue]
> operator doesn't work on bits.
>
> Is there an alternative to my query?
>
> Many thanks,
> Chris
>
>[/color]

[mountain man]

Re: min with a bit

"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
news:buocfi$ub9 $1@ucsnew1.ncl. ac.uk...[color=blue]
> "mountain man" <hobbit@souther n_seaweed.com.o p> wrote in message
> news:opOPb.2351 5$Wa.14455@news-server.bigpond. net.au...[color=green]
> > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > news:buoari$thc $1@ucsnew1.ncl. ac.uk...[/color]
>[color=green][color=darkred]
> > > Trying "select id, min(bit) from tab group by id" does not work, as[/color][/color][/color]
the[color=blue][color=green]
> > min[color=darkred]
> > > operator doesn't work on bits.
> > >
> > > Is there an alternative to my query?[/color][/color]
>[color=green]
> > min(cast(delete d as int))[/color]
>
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...[/color]


How about ..... min(cast(bit as varchar(1))) ?





Pete Brown
Falls Creek
Oz

[Gert-Jan Strik]

Re: min with a bit

When you move a database from MS Access to SQL-Server, then do not
translate MS-Access Boolean columns into SQL-Server Bit columns, but use
Tinyint or Char(1) columns instead (and add appropriate CHECK
constraints to limit the column to (0,1) or ('Y','N')).

HTH,
Gert-Jan


Not Me wrote:[color=blue]
>
> Hi,
>
> I'm trying to grab records with a priority over those marked as yes (-1) in
> a certain field.
>
> Trying "select id, min(bit) from tab group by id" does not work, as the min
> operator doesn't work on bits.
>
> Is there an alternative to my query?
>
> Many thanks,
> Chris[/color]

[Not Me]

Re: min with a bit

"Igor Raytsin" <n&i@cyberus.ca > wrote in message
news:400fee3f_1 @news.cybersurf .net...[color=blue]
> "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> news:buoari$thc $1@ucsnew1.ncl. ac.uk...[color=green]
> > I'm trying to grab records with a priority over those marked as yes (-1)[/color]
> in[color=green]
> > a certain field.
> >
> > Trying "select id, min(bit) from tab group by id" does not work, as the[/color]
> min[color=green]
> > operator doesn't work on bits.
> >
> > Is there an alternative to my query?[/color]
> How a bit could be (-1) ?[/color]

Ask Bill :o)

Chris

[Not Me]

Re: min with a bit

"Simon Hayes" <sql@hayes.ch > wrote in message
news:60cd0137.0 401220728.58b96 7ae@posting.goo gle.com...[color=blue]
> "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message[/color]
news:<buocfi$ub 9$1@ucsnew1.ncl .ac.uk>...[color=blue][color=green]
> > "mountain man" <hobbit@souther n_seaweed.com.o p> wrote in message
> > news:opOPb.2351 5$Wa.14455@news-server.bigpond. net.au...[color=darkred]
> > > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > > news:buoari$thc $1@ucsnew1.ncl. ac.uk...[/color]
> >[color=darkred]
> > > > Trying "select id, min(bit) from tab group by id" does not work, as[/color][/color][/color]
the[color=blue][color=green]
> > min[color=darkred]
> > > > operator doesn't work on bits.
> > > >
> > > > Is there an alternative to my query?[/color]
> >[color=darkred]
> > > min(cast(delete d as int))[/color]
> >
> > Thanks for that, I do though get an error when trying it, I guess it's
> > because I'm using an mdb file and linked tables to the sql server... any
> > other ideas? Could create a quick function I guess...[/color]
>
>
> Your question isn't really clear - a bit column can only hold 0,1 or
> NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
> an MSSQL query, then please consider posting the CREATE TABLE
> statement for your table, as well as the exact query that you're
> using, and the output you expect (sample data would also be useful).[/color]

Thanks for your help, yes the -1 just seems to be how access likes to
display the info.

The full problem, is that I have a table of, for example careers that people
have. In the table certain people (reference numbers) may have a current
job, and a number of non-current jobs. They may have no current job at all
but some past ones.

So, a table could show

id current job
#1 yes databases
#1 no graphics
#2 no statistics
#2 no games

and I would want to return one record for each id#, with a preference of a
current job (if no current job, any non-current job will do)

So far I've only managed to do a "select all current jobs union select all
non-current jobs that don't appear in the current jobs list" The problem
here is that it becomes very very slow when performing the "jobs that don't
appear in the current jobs list" (done by where x not in (select x from y)).

So my effort was to somehow group up the reference numbers, and display the
min(current) job, which would pick the current job as a preference. But the
problem here is I can't add min(job) to the list can I? because that will
not necessary return the correct job associated with the value of
min(current)..

Hope you understand the problem!!
Any help is greatly appreciated.

Cheers,
Chris

[Erland Sommarskog]

Re: min with a bit

Not Me (Not.Me@faker.f ake.fa.ke) writes:[color=blue]
> So, a table could show
>
> id current job
> #1 yes databases
> #1 no graphics
> #2 no statistics
> #2 no games
>
> and I would want to return one record for each id#, with a preference of a
> current job (if no current job, any non-current job will do)
>
> So far I've only managed to do a "select all current jobs union select
> all non-current jobs that don't appear in the current jobs list" The
> problem here is that it becomes very very slow when performing the "jobs
> that don't appear in the current jobs list" (done by where x not in
> (select x from y)).[/color]


Here is one way that you may want to try:

DECLARE @temp TABLE (ident int IDENTITY,
id int NOT NULL,
current bit NOT NULL,
job varchar(29) NOT NULL)

INSERT @temp(id, current, job)
SELECT id, current, job
FROM source_table
ORDER BY id, current DESC

SELECT t.id, c.current, t.job
FROM @temp t
JOIN (SELECT id, minident = MIN(ident)
FROM @temp
GROUP BY id) m ON t.ident = m.minident
ORDER BY t.id

By inserting the data into a table variable with an identity column,
the rows are numbered, and the first identity value for each id is the
row you want.

I should add that this trick is not foolproof. You are not really
guaranteed that the identity values actually reflects the ORDER BY
clause, but it works most of the time. Particularly, if there is
no parallelism. Here I am relying on that INSERT into a table variable
never uses parallelism.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp