[SQL] how to get row number in select query
Good morning, For postgresql 8.3, what is the system method/key word to get row number please? E.g., == lname1 gname1 lname2 gname2 lname3 gname3 .. I'd like to get 1 lname1 gname1 2 lname2 gname2 3 lname3 gname3 ... ... Something like select row_number?, lname, gname from Table1; Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to get row number in select query
Hi, Lu Ying. How do you define which row is #1 ? And #2 ? E.g. Ordered by lname? Or gname...? Best, Oliveiros - Original Message - From: "Emi Lu" To: Sent: Wednesday, January 26, 2011 4:11 PM Subject: [SQL] how to get row number in select query Good morning, For postgresql 8.3, what is the system method/key word to get row number please? E.g., == lname1 gname1 lname2 gname2 lname3 gname3 .. I'd like to get 1 lname1 gname1 2 lname2 gname2 3 lname3 gname3 ... ... Something like select row_number?, lname, gname from Table1; Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to get row number in select query
If it is to order in ascendent fashion by, say, lname, one possibility would be SELECT COUNT(b.*) as row_number, a.lname,a.gname FROM "Table1" a, "Table2" b WHERE a.lname >= b.lname GROUP BY a.lname,a.gname ORDER BY row_number If you want to order by gname just change the WHERE clause accordingly N.B. : This works as long as there is no repetition on the column you use to order. If there is, we'll need a way to tie break. What is your specific case? Also, note that this method is time consuming, and would work only for relatively small tables. AFAIK, version 8.3 doesn't have any "non-standard SQL" way to get a row number, but it is possible that something like that has been introduced in later versions... Best, Oliveiros - Original Message - From: "Emi Lu" To: Sent: Wednesday, January 26, 2011 4:11 PM Subject: [SQL] how to get row number in select query Good morning, For postgresql 8.3, what is the system method/key word to get row number please? E.g., == lname1 gname1 lname2 gname2 lname3 gname3 .. I'd like to get 1 lname1 gname1 2 lname2 gname2 3 lname3 gname3 ... ... Something like select row_number?, lname, gname from Table1; Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Compare the resulta of a count sql into bash
Hello,
I have the next :
COUNTONE=`/var/lib/pgsql/bin/./psql -U 'Thor' -d princlocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
COUNTTWO=`/var/lib/pgsql/bin/./psql -U 'Thor' -dprinclocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo
between '$FI' and '$FF'"
I want to compare the result countone with countwo how does it works ?
thnks
Re: [SQL] check files .backup
On 01/25/2011 11:14 PM, manuel antonio ochoa wrote: I would like it complete and not truncated? That it hasn't been modified since being originally written? OK. First, check the exit code from pg_backup so you detect any failures. The exit code is reported by the shell on UNIX systems in the $? variable, but you can usually just do something like the following (untested): if ! pg_backup ; then logger -p crit "pg_backup failed" mail -s "Pg backup failed" someone@yourcompany <<__END__ The latest backup failed. Check the logs for why, and fix it! __END__ fi Second: after writing the backup, calculate an md5sum or (preferably) digitally sign the backup using gpg. An md5sum is only really protection against corruption unless you store it somewhere separate and secure. I prefer to digitally sign my backups with detached gpg signatures. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Compare the resulta of a count sql into bash
On Wed, Jan 26, 2011 at 12:07 PM, manuel antonio ochoa
wrote:
>
> Hello,
> I have the next :
>
> COUNTONE=`/var/lib/pgsql/bin/./psql -U 'Thor' -d princlocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
> COUNTTWO=`/var/lib/pgsql/bin/./psql -U 'Thor' -dprinclocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo
> between '$FI' and '$FF'"
>
> I want to compare the result countone with countwo how does it works ?
It looks to me like COUNTONE and COUNTWO are going to get the strings
returned by psql, i.e. something not terribly helpful like: "count
--- 64 (1 row)". If you were expecting COUNTONE and COUNTWO to
hold exit codes, use the $? variable instead. But the exit code of
psql isn't what you want, either -- psql simply exits with something
like 0 or 1 depending on whether the command(s) succeeded.
Of course, you could cook up some script to parse the text returned by
psql to figure out the rowcounts, but that's a much greater pain than
just implementing whatever you're trying to do in a scripting language
using a PostgreSQL database adapter.
Josh
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Compare the resulta of a count sql into bash
On Wed, Jan 26, 2011 at 10:07 AM, manuel antonio ochoa
wrote:
>
> Hello,
> I have the next :
>
> COUNTONE=`/var/lib/pgsql/bin/./psql -U 'Thor' -d princlocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
> COUNTTWO=`/var/lib/pgsql/bin/./psql -U 'Thor' -dprinclocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo
> between '$FI' and '$FF'"
>
> I want to compare the result countone with countwo how does it works ?
echo $((COUNTONE-COUNTTWO));
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
